1"""
   2## Expressions
   3
   4Every AST node in SQLGlot is represented by a subclass of `Expression`.
   5
   6This module contains the implementation of all supported `Expression` types. Additionally,
   7it exposes a number of helper functions, which are mainly used to programmatically build
   8SQL expressions, such as `sqlglot.expressions.select`.
   9
  10----
  11"""
  12
  13from __future__ import annotations
  14
  15import datetime
  16import math
  17import numbers
  18import re
  19import typing as t
  20from collections import deque
  21from copy import deepcopy
  22from enum import auto
  23from functools import reduce
  24
  25from sqlglot._typing import E
  26from sqlglot.errors import ErrorLevel, ParseError
  27from sqlglot.helper import (
  28    AutoName,
  29    camel_to_snake_case,
  30    ensure_collection,
  31    ensure_list,
  32    seq_get,
  33    subclasses,
  34)
  35from sqlglot.tokens import Token
  36
  37if t.TYPE_CHECKING:
  38    from sqlglot.dialects.dialect import DialectType
  39
  40
  41class _Expression(type):
  42    def __new__(cls, clsname, bases, attrs):
  43        klass = super().__new__(cls, clsname, bases, attrs)
  44
  45        # When an Expression class is created, its key is automatically set to be
  46        # the lowercase version of the class' name.
  47        klass.key = clsname.lower()
  48
  49        # This is so that docstrings are not inherited in pdoc
  50        klass.__doc__ = klass.__doc__ or ""
  51
  52        return klass
  53
  54
  55SQLGLOT_META = "sqlglot.meta"
  56
  57
  58class Expression(metaclass=_Expression):
  59    """
  60    The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary
  61    context, such as its child expressions, their names (arg keys), and whether a given child expression
  62    is optional or not.
  63
  64    Attributes:
  65        key: a unique key for each class in the Expression hierarchy. This is useful for hashing
  66            and representing expressions as strings.
  67        arg_types: determines what arguments (child nodes) are supported by an expression. It
  68            maps arg keys to booleans that indicate whether the corresponding args are optional.
  69        parent: a reference to the parent expression (or None, in case of root expressions).
  70        arg_key: the arg key an expression is associated with, i.e. the name its parent expression
  71            uses to refer to it.
  72        comments: a list of comments that are associated with a given expression. This is used in
  73            order to preserve comments when transpiling SQL code.
  74        type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the
  75            optimizer, in order to enable some transformations that require type information.
  76        meta: a dictionary that can be used to store useful metadata for a given expression.
  77
  78    Example:
  79        >>> class Foo(Expression):
  80        ...     arg_types = {"this": True, "expression": False}
  81
  82        The above definition informs us that Foo is an Expression that requires an argument called
  83        "this" and may also optionally receive an argument called "expression".
  84
  85    Args:
  86        args: a mapping used for retrieving the arguments of an expression, given their arg keys.
  87    """
  88
  89    key = "expression"
  90    arg_types = {"this": True}
  91    __slots__ = ("args", "parent", "arg_key", "comments", "_type", "_meta", "_hash")
  92
  93    def __init__(self, **args: t.Any):
  94        self.args: t.Dict[str, t.Any] = args
  95        self.parent: t.Optional[Expression] = None
  96        self.arg_key: t.Optional[str] = None
  97        self.comments: t.Optional[t.List[str]] = None
  98        self._type: t.Optional[DataType] = None
  99        self._meta: t.Optional[t.Dict[str, t.Any]] = None
 100        self._hash: t.Optional[int] = None
 101
 102        for arg_key, value in self.args.items():
 103            self._set_parent(arg_key, value)
 104
 105    def __eq__(self, other) -> bool:
 106        return type(self) is type(other) and hash(self) == hash(other)
 107
 108    @property
 109    def hashable_args(self) -> t.Any:
 110        return frozenset(
 111            (k, tuple(_norm_arg(a) for a in v) if type(v) is list else _norm_arg(v))
 112            for k, v in self.args.items()
 113            if not (v is None or v is False or (type(v) is list and not v))
 114        )
 115
 116    def __hash__(self) -> int:
 117        if self._hash is not None:
 118            return self._hash
 119
 120        return hash((self.__class__, self.hashable_args))
 121
 122    @property
 123    def this(self) -> t.Any:
 124        """
 125        Retrieves the argument with key "this".
 126        """
 127        return self.args.get("this")
 128
 129    @property
 130    def expression(self) -> t.Any:
 131        """
 132        Retrieves the argument with key "expression".
 133        """
 134        return self.args.get("expression")
 135
 136    @property
 137    def expressions(self):
 138        """
 139        Retrieves the argument with key "expressions".
 140        """
 141        return self.args.get("expressions") or []
 142
 143    def text(self, key) -> str:
 144        """
 145        Returns a textual representation of the argument corresponding to "key". This can only be used
 146        for args that are strings or leaf Expression instances, such as identifiers and literals.
 147        """
 148        field = self.args.get(key)
 149        if isinstance(field, str):
 150            return field
 151        if isinstance(field, (Identifier, Literal, Var)):
 152            return field.this
 153        if isinstance(field, (Star, Null)):
 154            return field.name
 155        return ""
 156
 157    @property
 158    def is_string(self) -> bool:
 159        """
 160        Checks whether a Literal expression is a string.
 161        """
 162        return isinstance(self, Literal) and self.args["is_string"]
 163
 164    @property
 165    def is_number(self) -> bool:
 166        """
 167        Checks whether a Literal expression is a number.
 168        """
 169        return isinstance(self, Literal) and not self.args["is_string"]
 170
 171    @property
 172    def is_int(self) -> bool:
 173        """
 174        Checks whether a Literal expression is an integer.
 175        """
 176        if self.is_number:
 177            try:
 178                int(self.name)
 179                return True
 180            except ValueError:
 181                pass
 182        return False
 183
 184    @property
 185    def is_star(self) -> bool:
 186        """Checks whether an expression is a star."""
 187        return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star))
 188
 189    @property
 190    def alias(self) -> str:
 191        """
 192        Returns the alias of the expression, or an empty string if it's not aliased.
 193        """
 194        if isinstance(self.args.get("alias"), TableAlias):
 195            return self.args["alias"].name
 196        return self.text("alias")
 197
 198    @property
 199    def alias_column_names(self) -> t.List[str]:
 200        table_alias = self.args.get("alias")
 201        if not table_alias:
 202            return []
 203        return [c.name for c in table_alias.args.get("columns") or []]
 204
 205    @property
 206    def name(self) -> str:
 207        return self.text("this")
 208
 209    @property
 210    def alias_or_name(self) -> str:
 211        return self.alias or self.name
 212
 213    @property
 214    def output_name(self) -> str:
 215        """
 216        Name of the output column if this expression is a selection.
 217
 218        If the Expression has no output name, an empty string is returned.
 219
 220        Example:
 221            >>> from sqlglot import parse_one
 222            >>> parse_one("SELECT a").expressions[0].output_name
 223            'a'
 224            >>> parse_one("SELECT b AS c").expressions[0].output_name
 225            'c'
 226            >>> parse_one("SELECT 1 + 2").expressions[0].output_name
 227            ''
 228        """
 229        return ""
 230
 231    @property
 232    def type(self) -> t.Optional[DataType]:
 233        return self._type
 234
 235    @type.setter
 236    def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None:
 237        if dtype and not isinstance(dtype, DataType):
 238            dtype = DataType.build(dtype)
 239        self._type = dtype  # type: ignore
 240
 241    @property
 242    def meta(self) -> t.Dict[str, t.Any]:
 243        if self._meta is None:
 244            self._meta = {}
 245        return self._meta
 246
 247    def __deepcopy__(self, memo):
 248        copy = self.__class__(**deepcopy(self.args))
 249        if self.comments is not None:
 250            copy.comments = deepcopy(self.comments)
 251
 252        if self._type is not None:
 253            copy._type = self._type.copy()
 254
 255        if self._meta is not None:
 256            copy._meta = deepcopy(self._meta)
 257
 258        return copy
 259
 260    def copy(self):
 261        """
 262        Returns a deep copy of the expression.
 263        """
 264        new = deepcopy(self)
 265        new.parent = self.parent
 266        return new
 267
 268    def add_comments(self, comments: t.Optional[t.List[str]]) -> None:
 269        if self.comments is None:
 270            self.comments = []
 271        if comments:
 272            for comment in comments:
 273                _, *meta = comment.split(SQLGLOT_META)
 274                if meta:
 275                    for kv in "".join(meta).split(","):
 276                        k, *v = kv.split("=")
 277                        value = v[0].strip() if v else True
 278                        self.meta[k.strip()] = value
 279                self.comments.append(comment)
 280
 281    def append(self, arg_key: str, value: t.Any) -> None:
 282        """
 283        Appends value to arg_key if it's a list or sets it as a new list.
 284
 285        Args:
 286            arg_key (str): name of the list expression arg
 287            value (Any): value to append to the list
 288        """
 289        if not isinstance(self.args.get(arg_key), list):
 290            self.args[arg_key] = []
 291        self.args[arg_key].append(value)
 292        self._set_parent(arg_key, value)
 293
 294    def set(self, arg_key: str, value: t.Any) -> None:
 295        """
 296        Sets arg_key to value.
 297
 298        Args:
 299            arg_key: name of the expression arg.
 300            value: value to set the arg to.
 301        """
 302        if value is None:
 303            self.args.pop(arg_key, None)
 304            return
 305
 306        self.args[arg_key] = value
 307        self._set_parent(arg_key, value)
 308
 309    def _set_parent(self, arg_key: str, value: t.Any) -> None:
 310        if hasattr(value, "parent"):
 311            value.parent = self
 312            value.arg_key = arg_key
 313        elif type(value) is list:
 314            for v in value:
 315                if hasattr(v, "parent"):
 316                    v.parent = self
 317                    v.arg_key = arg_key
 318
 319    @property
 320    def depth(self) -> int:
 321        """
 322        Returns the depth of this tree.
 323        """
 324        if self.parent:
 325            return self.parent.depth + 1
 326        return 0
 327
 328    def iter_expressions(self) -> t.Iterator[t.Tuple[str, Expression]]:
 329        """Yields the key and expression for all arguments, exploding list args."""
 330        for k, vs in self.args.items():
 331            if type(vs) is list:
 332                for v in vs:
 333                    if hasattr(v, "parent"):
 334                        yield k, v
 335            else:
 336                if hasattr(vs, "parent"):
 337                    yield k, vs
 338
 339    def find(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Optional[E]:
 340        """
 341        Returns the first node in this tree which matches at least one of
 342        the specified types.
 343
 344        Args:
 345            expression_types: the expression type(s) to match.
 346            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 347
 348        Returns:
 349            The node which matches the criteria or None if no such node was found.
 350        """
 351        return next(self.find_all(*expression_types, bfs=bfs), None)
 352
 353    def find_all(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Iterator[E]:
 354        """
 355        Returns a generator object which visits all nodes in this tree and only
 356        yields those that match at least one of the specified expression types.
 357
 358        Args:
 359            expression_types: the expression type(s) to match.
 360            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 361
 362        Returns:
 363            The generator object.
 364        """
 365        for expression, *_ in self.walk(bfs=bfs):
 366            if isinstance(expression, expression_types):
 367                yield expression
 368
 369    def find_ancestor(self, *expression_types: t.Type[E]) -> t.Optional[E]:
 370        """
 371        Returns a nearest parent matching expression_types.
 372
 373        Args:
 374            expression_types: the expression type(s) to match.
 375
 376        Returns:
 377            The parent node.
 378        """
 379        ancestor = self.parent
 380        while ancestor and not isinstance(ancestor, expression_types):
 381            ancestor = ancestor.parent
 382        return t.cast(E, ancestor)
 383
 384    @property
 385    def parent_select(self) -> t.Optional[Select]:
 386        """
 387        Returns the parent select statement.
 388        """
 389        return self.find_ancestor(Select)
 390
 391    @property
 392    def same_parent(self) -> bool:
 393        """Returns if the parent is the same class as itself."""
 394        return type(self.parent) is self.__class__
 395
 396    def root(self) -> Expression:
 397        """
 398        Returns the root expression of this tree.
 399        """
 400        expression = self
 401        while expression.parent:
 402            expression = expression.parent
 403        return expression
 404
 405    def walk(self, bfs=True, prune=None):
 406        """
 407        Returns a generator object which visits all nodes in this tree.
 408
 409        Args:
 410            bfs (bool): if set to True the BFS traversal order will be applied,
 411                otherwise the DFS traversal will be used instead.
 412            prune ((node, parent, arg_key) -> bool): callable that returns True if
 413                the generator should stop traversing this branch of the tree.
 414
 415        Returns:
 416            the generator object.
 417        """
 418        if bfs:
 419            yield from self.bfs(prune=prune)
 420        else:
 421            yield from self.dfs(prune=prune)
 422
 423    def dfs(self, parent=None, key=None, prune=None):
 424        """
 425        Returns a generator object which visits all nodes in this tree in
 426        the DFS (Depth-first) order.
 427
 428        Returns:
 429            The generator object.
 430        """
 431        parent = parent or self.parent
 432        yield self, parent, key
 433        if prune and prune(self, parent, key):
 434            return
 435
 436        for k, v in self.iter_expressions():
 437            yield from v.dfs(self, k, prune)
 438
 439    def bfs(self, prune=None):
 440        """
 441        Returns a generator object which visits all nodes in this tree in
 442        the BFS (Breadth-first) order.
 443
 444        Returns:
 445            The generator object.
 446        """
 447        queue = deque([(self, self.parent, None)])
 448
 449        while queue:
 450            item, parent, key = queue.popleft()
 451
 452            yield item, parent, key
 453            if prune and prune(item, parent, key):
 454                continue
 455
 456            for k, v in item.iter_expressions():
 457                queue.append((v, item, k))
 458
 459    def unnest(self):
 460        """
 461        Returns the first non parenthesis child or self.
 462        """
 463        expression = self
 464        while type(expression) is Paren:
 465            expression = expression.this
 466        return expression
 467
 468    def unalias(self):
 469        """
 470        Returns the inner expression if this is an Alias.
 471        """
 472        if isinstance(self, Alias):
 473            return self.this
 474        return self
 475
 476    def unnest_operands(self):
 477        """
 478        Returns unnested operands as a tuple.
 479        """
 480        return tuple(arg.unnest() for _, arg in self.iter_expressions())
 481
 482    def flatten(self, unnest=True):
 483        """
 484        Returns a generator which yields child nodes who's parents are the same class.
 485
 486        A AND B AND C -> [A, B, C]
 487        """
 488        for node, _, _ in self.dfs(prune=lambda n, p, *_: p and not type(n) is self.__class__):
 489            if not type(node) is self.__class__:
 490                yield node.unnest() if unnest and not isinstance(node, Subquery) else node
 491
 492    def __str__(self) -> str:
 493        return self.sql()
 494
 495    def __repr__(self) -> str:
 496        return self._to_s()
 497
 498    def sql(self, dialect: DialectType = None, **opts) -> str:
 499        """
 500        Returns SQL string representation of this tree.
 501
 502        Args:
 503            dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
 504            opts: other `sqlglot.generator.Generator` options.
 505
 506        Returns:
 507            The SQL string.
 508        """
 509        from sqlglot.dialects import Dialect
 510
 511        return Dialect.get_or_raise(dialect)().generate(self, **opts)
 512
 513    def _to_s(self, hide_missing: bool = True, level: int = 0) -> str:
 514        indent = "" if not level else "\n"
 515        indent += "".join(["  "] * level)
 516        left = f"({self.key.upper()} "
 517
 518        args: t.Dict[str, t.Any] = {
 519            k: ", ".join(
 520                v._to_s(hide_missing=hide_missing, level=level + 1)
 521                if hasattr(v, "_to_s")
 522                else str(v)
 523                for v in ensure_list(vs)
 524                if v is not None
 525            )
 526            for k, vs in self.args.items()
 527        }
 528        args["comments"] = self.comments
 529        args["type"] = self.type
 530        args = {k: v for k, v in args.items() if v or not hide_missing}
 531
 532        right = ", ".join(f"{k}: {v}" for k, v in args.items())
 533        right += ")"
 534
 535        return indent + left + right
 536
 537    def transform(self, fun, *args, copy=True, **kwargs):
 538        """
 539        Recursively visits all tree nodes (excluding already transformed ones)
 540        and applies the given transformation function to each node.
 541
 542        Args:
 543            fun (function): a function which takes a node as an argument and returns a
 544                new transformed node or the same node without modifications. If the function
 545                returns None, then the corresponding node will be removed from the syntax tree.
 546            copy (bool): if set to True a new tree instance is constructed, otherwise the tree is
 547                modified in place.
 548
 549        Returns:
 550            The transformed tree.
 551        """
 552        node = self.copy() if copy else self
 553        new_node = fun(node, *args, **kwargs)
 554
 555        if new_node is None or not isinstance(new_node, Expression):
 556            return new_node
 557        if new_node is not node:
 558            new_node.parent = node.parent
 559            return new_node
 560
 561        replace_children(new_node, lambda child: child.transform(fun, *args, copy=False, **kwargs))
 562        return new_node
 563
 564    @t.overload
 565    def replace(self, expression: E) -> E:
 566        ...
 567
 568    @t.overload
 569    def replace(self, expression: None) -> None:
 570        ...
 571
 572    def replace(self, expression):
 573        """
 574        Swap out this expression with a new expression.
 575
 576        For example::
 577
 578            >>> tree = Select().select("x").from_("tbl")
 579            >>> tree.find(Column).replace(Column(this="y"))
 580            (COLUMN this: y)
 581            >>> tree.sql()
 582            'SELECT y FROM tbl'
 583
 584        Args:
 585            expression: new node
 586
 587        Returns:
 588            The new expression or expressions.
 589        """
 590        if not self.parent:
 591            return expression
 592
 593        parent = self.parent
 594        self.parent = None
 595
 596        replace_children(parent, lambda child: expression if child is self else child)
 597        return expression
 598
 599    def pop(self: E) -> E:
 600        """
 601        Remove this expression from its AST.
 602
 603        Returns:
 604            The popped expression.
 605        """
 606        self.replace(None)
 607        return self
 608
 609    def assert_is(self, type_: t.Type[E]) -> E:
 610        """
 611        Assert that this `Expression` is an instance of `type_`.
 612
 613        If it is NOT an instance of `type_`, this raises an assertion error.
 614        Otherwise, this returns this expression.
 615
 616        Examples:
 617            This is useful for type security in chained expressions:
 618
 619            >>> import sqlglot
 620            >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql()
 621            'SELECT x, z FROM y'
 622        """
 623        assert isinstance(self, type_)
 624        return self
 625
 626    def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]:
 627        """
 628        Checks if this expression is valid (e.g. all mandatory args are set).
 629
 630        Args:
 631            args: a sequence of values that were used to instantiate a Func expression. This is used
 632                to check that the provided arguments don't exceed the function argument limit.
 633
 634        Returns:
 635            A list of error messages for all possible errors that were found.
 636        """
 637        errors: t.List[str] = []
 638
 639        for k in self.args:
 640            if k not in self.arg_types:
 641                errors.append(f"Unexpected keyword: '{k}' for {self.__class__}")
 642        for k, mandatory in self.arg_types.items():
 643            v = self.args.get(k)
 644            if mandatory and (v is None or (isinstance(v, list) and not v)):
 645                errors.append(f"Required keyword: '{k}' missing for {self.__class__}")
 646
 647        if (
 648            args
 649            and isinstance(self, Func)
 650            and len(args) > len(self.arg_types)
 651            and not self.is_var_len_args
 652        ):
 653            errors.append(
 654                f"The number of provided arguments ({len(args)}) is greater than "
 655                f"the maximum number of supported arguments ({len(self.arg_types)})"
 656            )
 657
 658        return errors
 659
 660    def dump(self):
 661        """
 662        Dump this Expression to a JSON-serializable dict.
 663        """
 664        from sqlglot.serde import dump
 665
 666        return dump(self)
 667
 668    @classmethod
 669    def load(cls, obj):
 670        """
 671        Load a dict (as returned by `Expression.dump`) into an Expression instance.
 672        """
 673        from sqlglot.serde import load
 674
 675        return load(obj)
 676
 677    def and_(
 678        self,
 679        *expressions: t.Optional[ExpOrStr],
 680        dialect: DialectType = None,
 681        copy: bool = True,
 682        **opts,
 683    ) -> Condition:
 684        """
 685        AND this condition with one or multiple expressions.
 686
 687        Example:
 688            >>> condition("x=1").and_("y=1").sql()
 689            'x = 1 AND y = 1'
 690
 691        Args:
 692            *expressions: the SQL code strings to parse.
 693                If an `Expression` instance is passed, it will be used as-is.
 694            dialect: the dialect used to parse the input expression.
 695            copy: whether or not to copy the involved expressions (only applies to Expressions).
 696            opts: other options to use to parse the input expressions.
 697
 698        Returns:
 699            The new And condition.
 700        """
 701        return and_(self, *expressions, dialect=dialect, copy=copy, **opts)
 702
 703    def or_(
 704        self,
 705        *expressions: t.Optional[ExpOrStr],
 706        dialect: DialectType = None,
 707        copy: bool = True,
 708        **opts,
 709    ) -> Condition:
 710        """
 711        OR this condition with one or multiple expressions.
 712
 713        Example:
 714            >>> condition("x=1").or_("y=1").sql()
 715            'x = 1 OR y = 1'
 716
 717        Args:
 718            *expressions: the SQL code strings to parse.
 719                If an `Expression` instance is passed, it will be used as-is.
 720            dialect: the dialect used to parse the input expression.
 721            copy: whether or not to copy the involved expressions (only applies to Expressions).
 722            opts: other options to use to parse the input expressions.
 723
 724        Returns:
 725            The new Or condition.
 726        """
 727        return or_(self, *expressions, dialect=dialect, copy=copy, **opts)
 728
 729    def not_(self, copy: bool = True):
 730        """
 731        Wrap this condition with NOT.
 732
 733        Example:
 734            >>> condition("x=1").not_().sql()
 735            'NOT x = 1'
 736
 737        Args:
 738            copy: whether or not to copy this object.
 739
 740        Returns:
 741            The new Not instance.
 742        """
 743        return not_(self, copy=copy)
 744
 745    def as_(
 746        self,
 747        alias: str | Identifier,
 748        quoted: t.Optional[bool] = None,
 749        dialect: DialectType = None,
 750        copy: bool = True,
 751        **opts,
 752    ) -> Alias:
 753        return alias_(self, alias, quoted=quoted, dialect=dialect, copy=copy, **opts)
 754
 755    def _binop(self, klass: t.Type[E], other: t.Any, reverse: bool = False) -> E:
 756        this = self.copy()
 757        other = convert(other, copy=True)
 758        if not isinstance(this, klass) and not isinstance(other, klass):
 759            this = _wrap(this, Binary)
 760            other = _wrap(other, Binary)
 761        if reverse:
 762            return klass(this=other, expression=this)
 763        return klass(this=this, expression=other)
 764
 765    def __getitem__(self, other: ExpOrStr | t.Tuple[ExpOrStr]) -> Bracket:
 766        return Bracket(
 767            this=self.copy(), expressions=[convert(e, copy=True) for e in ensure_list(other)]
 768        )
 769
 770    def __iter__(self) -> t.Iterator:
 771        if "expressions" in self.arg_types:
 772            return iter(self.args.get("expressions") or [])
 773        # We define this because __getitem__ converts Expression into an iterable, which is
 774        # problematic because one can hit infinite loops if they do "for x in some_expr: ..."
 775        # See: https://peps.python.org/pep-0234/
 776        raise TypeError(f"'{self.__class__.__name__}' object is not iterable")
 777
 778    def isin(
 779        self,
 780        *expressions: t.Any,
 781        query: t.Optional[ExpOrStr] = None,
 782        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
 783        copy: bool = True,
 784        **opts,
 785    ) -> In:
 786        return In(
 787            this=maybe_copy(self, copy),
 788            expressions=[convert(e, copy=copy) for e in expressions],
 789            query=maybe_parse(query, copy=copy, **opts) if query else None,
 790            unnest=Unnest(
 791                expressions=[
 792                    maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts) for e in ensure_list(unnest)
 793                ]
 794            )
 795            if unnest
 796            else None,
 797        )
 798
 799    def between(self, low: t.Any, high: t.Any, copy: bool = True, **opts) -> Between:
 800        return Between(
 801            this=maybe_copy(self, copy),
 802            low=convert(low, copy=copy, **opts),
 803            high=convert(high, copy=copy, **opts),
 804        )
 805
 806    def is_(self, other: ExpOrStr) -> Is:
 807        return self._binop(Is, other)
 808
 809    def like(self, other: ExpOrStr) -> Like:
 810        return self._binop(Like, other)
 811
 812    def ilike(self, other: ExpOrStr) -> ILike:
 813        return self._binop(ILike, other)
 814
 815    def eq(self, other: t.Any) -> EQ:
 816        return self._binop(EQ, other)
 817
 818    def neq(self, other: t.Any) -> NEQ:
 819        return self._binop(NEQ, other)
 820
 821    def rlike(self, other: ExpOrStr) -> RegexpLike:
 822        return self._binop(RegexpLike, other)
 823
 824    def __lt__(self, other: t.Any) -> LT:
 825        return self._binop(LT, other)
 826
 827    def __le__(self, other: t.Any) -> LTE:
 828        return self._binop(LTE, other)
 829
 830    def __gt__(self, other: t.Any) -> GT:
 831        return self._binop(GT, other)
 832
 833    def __ge__(self, other: t.Any) -> GTE:
 834        return self._binop(GTE, other)
 835
 836    def __add__(self, other: t.Any) -> Add:
 837        return self._binop(Add, other)
 838
 839    def __radd__(self, other: t.Any) -> Add:
 840        return self._binop(Add, other, reverse=True)
 841
 842    def __sub__(self, other: t.Any) -> Sub:
 843        return self._binop(Sub, other)
 844
 845    def __rsub__(self, other: t.Any) -> Sub:
 846        return self._binop(Sub, other, reverse=True)
 847
 848    def __mul__(self, other: t.Any) -> Mul:
 849        return self._binop(Mul, other)
 850
 851    def __rmul__(self, other: t.Any) -> Mul:
 852        return self._binop(Mul, other, reverse=True)
 853
 854    def __truediv__(self, other: t.Any) -> Div:
 855        return self._binop(Div, other)
 856
 857    def __rtruediv__(self, other: t.Any) -> Div:
 858        return self._binop(Div, other, reverse=True)
 859
 860    def __floordiv__(self, other: t.Any) -> IntDiv:
 861        return self._binop(IntDiv, other)
 862
 863    def __rfloordiv__(self, other: t.Any) -> IntDiv:
 864        return self._binop(IntDiv, other, reverse=True)
 865
 866    def __mod__(self, other: t.Any) -> Mod:
 867        return self._binop(Mod, other)
 868
 869    def __rmod__(self, other: t.Any) -> Mod:
 870        return self._binop(Mod, other, reverse=True)
 871
 872    def __pow__(self, other: t.Any) -> Pow:
 873        return self._binop(Pow, other)
 874
 875    def __rpow__(self, other: t.Any) -> Pow:
 876        return self._binop(Pow, other, reverse=True)
 877
 878    def __and__(self, other: t.Any) -> And:
 879        return self._binop(And, other)
 880
 881    def __rand__(self, other: t.Any) -> And:
 882        return self._binop(And, other, reverse=True)
 883
 884    def __or__(self, other: t.Any) -> Or:
 885        return self._binop(Or, other)
 886
 887    def __ror__(self, other: t.Any) -> Or:
 888        return self._binop(Or, other, reverse=True)
 889
 890    def __neg__(self) -> Neg:
 891        return Neg(this=_wrap(self.copy(), Binary))
 892
 893    def __invert__(self) -> Not:
 894        return not_(self.copy())
 895
 896
 897IntoType = t.Union[
 898    str,
 899    t.Type[Expression],
 900    t.Collection[t.Union[str, t.Type[Expression]]],
 901]
 902ExpOrStr = t.Union[str, Expression]
 903
 904
 905class Condition(Expression):
 906    """Logical conditions like x AND y, or simply x"""
 907
 908
 909class Predicate(Condition):
 910    """Relationships like x = y, x > 1, x >= y."""
 911
 912
 913class DerivedTable(Expression):
 914    @property
 915    def selects(self) -> t.List[Expression]:
 916        return self.this.selects if isinstance(self.this, Subqueryable) else []
 917
 918    @property
 919    def named_selects(self) -> t.List[str]:
 920        return [select.output_name for select in self.selects]
 921
 922
 923class Unionable(Expression):
 924    def union(
 925        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 926    ) -> Unionable:
 927        """
 928        Builds a UNION expression.
 929
 930        Example:
 931            >>> import sqlglot
 932            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
 933            'SELECT * FROM foo UNION SELECT * FROM bla'
 934
 935        Args:
 936            expression: the SQL code string.
 937                If an `Expression` instance is passed, it will be used as-is.
 938            distinct: set the DISTINCT flag if and only if this is true.
 939            dialect: the dialect used to parse the input expression.
 940            opts: other options to use to parse the input expressions.
 941
 942        Returns:
 943            The new Union expression.
 944        """
 945        return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 946
 947    def intersect(
 948        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 949    ) -> Unionable:
 950        """
 951        Builds an INTERSECT expression.
 952
 953        Example:
 954            >>> import sqlglot
 955            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
 956            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
 957
 958        Args:
 959            expression: the SQL code string.
 960                If an `Expression` instance is passed, it will be used as-is.
 961            distinct: set the DISTINCT flag if and only if this is true.
 962            dialect: the dialect used to parse the input expression.
 963            opts: other options to use to parse the input expressions.
 964
 965        Returns:
 966            The new Intersect expression.
 967        """
 968        return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 969
 970    def except_(
 971        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 972    ) -> Unionable:
 973        """
 974        Builds an EXCEPT expression.
 975
 976        Example:
 977            >>> import sqlglot
 978            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
 979            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
 980
 981        Args:
 982            expression: the SQL code string.
 983                If an `Expression` instance is passed, it will be used as-is.
 984            distinct: set the DISTINCT flag if and only if this is true.
 985            dialect: the dialect used to parse the input expression.
 986            opts: other options to use to parse the input expressions.
 987
 988        Returns:
 989            The new Except expression.
 990        """
 991        return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 992
 993
 994class UDTF(DerivedTable, Unionable):
 995    @property
 996    def selects(self) -> t.List[Expression]:
 997        alias = self.args.get("alias")
 998        return alias.columns if alias else []
 999
1000
1001class Cache(Expression):
1002    arg_types = {
1003        "with": False,
1004        "this": True,
1005        "lazy": False,
1006        "options": False,
1007        "expression": False,
1008    }
1009
1010
1011class Uncache(Expression):
1012    arg_types = {"this": True, "exists": False}
1013
1014
1015class DDL(Expression):
1016    @property
1017    def ctes(self):
1018        with_ = self.args.get("with")
1019        if not with_:
1020            return []
1021        return with_.expressions
1022
1023    @property
1024    def named_selects(self) -> t.List[str]:
1025        if isinstance(self.expression, Subqueryable):
1026            return self.expression.named_selects
1027        return []
1028
1029    @property
1030    def selects(self) -> t.List[Expression]:
1031        if isinstance(self.expression, Subqueryable):
1032            return self.expression.selects
1033        return []
1034
1035
1036class Create(DDL):
1037    arg_types = {
1038        "with": False,
1039        "this": True,
1040        "kind": True,
1041        "expression": False,
1042        "exists": False,
1043        "properties": False,
1044        "replace": False,
1045        "unique": False,
1046        "indexes": False,
1047        "no_schema_binding": False,
1048        "begin": False,
1049        "end": False,
1050        "clone": False,
1051    }
1052
1053
1054# https://docs.snowflake.com/en/sql-reference/sql/create-clone
1055# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_clone_statement
1056# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_copy
1057class Clone(Expression):
1058    arg_types = {
1059        "this": True,
1060        "when": False,
1061        "kind": False,
1062        "shallow": False,
1063        "expression": False,
1064        "copy": False,
1065    }
1066
1067
1068class Describe(Expression):
1069    arg_types = {"this": True, "kind": False, "expressions": False}
1070
1071
1072class Kill(Expression):
1073    arg_types = {"this": True, "kind": False}
1074
1075
1076class Pragma(Expression):
1077    pass
1078
1079
1080class Set(Expression):
1081    arg_types = {"expressions": False, "unset": False, "tag": False}
1082
1083
1084class SetItem(Expression):
1085    arg_types = {
1086        "this": False,
1087        "expressions": False,
1088        "kind": False,
1089        "collate": False,  # MySQL SET NAMES statement
1090        "global": False,
1091    }
1092
1093
1094class Show(Expression):
1095    arg_types = {
1096        "this": True,
1097        "target": False,
1098        "offset": False,
1099        "limit": False,
1100        "like": False,
1101        "where": False,
1102        "db": False,
1103        "scope": False,
1104        "scope_kind": False,
1105        "full": False,
1106        "mutex": False,
1107        "query": False,
1108        "channel": False,
1109        "global": False,
1110        "log": False,
1111        "position": False,
1112        "types": False,
1113    }
1114
1115
1116class UserDefinedFunction(Expression):
1117    arg_types = {"this": True, "expressions": False, "wrapped": False}
1118
1119
1120class CharacterSet(Expression):
1121    arg_types = {"this": True, "default": False}
1122
1123
1124class With(Expression):
1125    arg_types = {"expressions": True, "recursive": False}
1126
1127    @property
1128    def recursive(self) -> bool:
1129        return bool(self.args.get("recursive"))
1130
1131
1132class WithinGroup(Expression):
1133    arg_types = {"this": True, "expression": False}
1134
1135
1136class CTE(DerivedTable):
1137    arg_types = {"this": True, "alias": True}
1138
1139
1140class TableAlias(Expression):
1141    arg_types = {"this": False, "columns": False}
1142
1143    @property
1144    def columns(self):
1145        return self.args.get("columns") or []
1146
1147
1148class BitString(Condition):
1149    pass
1150
1151
1152class HexString(Condition):
1153    pass
1154
1155
1156class ByteString(Condition):
1157    pass
1158
1159
1160class RawString(Condition):
1161    pass
1162
1163
1164class Column(Condition):
1165    arg_types = {"this": True, "table": False, "db": False, "catalog": False, "join_mark": False}
1166
1167    @property
1168    def table(self) -> str:
1169        return self.text("table")
1170
1171    @property
1172    def db(self) -> str:
1173        return self.text("db")
1174
1175    @property
1176    def catalog(self) -> str:
1177        return self.text("catalog")
1178
1179    @property
1180    def output_name(self) -> str:
1181        return self.name
1182
1183    @property
1184    def parts(self) -> t.List[Identifier]:
1185        """Return the parts of a column in order catalog, db, table, name."""
1186        return [
1187            t.cast(Identifier, self.args[part])
1188            for part in ("catalog", "db", "table", "this")
1189            if self.args.get(part)
1190        ]
1191
1192    def to_dot(self) -> Dot | Identifier:
1193        """Converts the column into a dot expression."""
1194        parts = self.parts
1195        parent = self.parent
1196
1197        while parent:
1198            if isinstance(parent, Dot):
1199                parts.append(parent.expression)
1200            parent = parent.parent
1201
1202        return Dot.build(deepcopy(parts)) if len(parts) > 1 else parts[0]
1203
1204
1205class ColumnPosition(Expression):
1206    arg_types = {"this": False, "position": True}
1207
1208
1209class ColumnDef(Expression):
1210    arg_types = {
1211        "this": True,
1212        "kind": False,
1213        "constraints": False,
1214        "exists": False,
1215        "position": False,
1216    }
1217
1218    @property
1219    def constraints(self) -> t.List[ColumnConstraint]:
1220        return self.args.get("constraints") or []
1221
1222
1223class AlterColumn(Expression):
1224    arg_types = {
1225        "this": True,
1226        "dtype": False,
1227        "collate": False,
1228        "using": False,
1229        "default": False,
1230        "drop": False,
1231    }
1232
1233
1234class RenameTable(Expression):
1235    pass
1236
1237
1238class SwapTable(Expression):
1239    pass
1240
1241
1242class Comment(Expression):
1243    arg_types = {"this": True, "kind": True, "expression": True, "exists": False}
1244
1245
1246class Comprehension(Expression):
1247    arg_types = {"this": True, "expression": True, "iterator": True, "condition": False}
1248
1249
1250# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1251class MergeTreeTTLAction(Expression):
1252    arg_types = {
1253        "this": True,
1254        "delete": False,
1255        "recompress": False,
1256        "to_disk": False,
1257        "to_volume": False,
1258    }
1259
1260
1261# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1262class MergeTreeTTL(Expression):
1263    arg_types = {
1264        "expressions": True,
1265        "where": False,
1266        "group": False,
1267        "aggregates": False,
1268    }
1269
1270
1271# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1272class IndexConstraintOption(Expression):
1273    arg_types = {
1274        "key_block_size": False,
1275        "using": False,
1276        "parser": False,
1277        "comment": False,
1278        "visible": False,
1279        "engine_attr": False,
1280        "secondary_engine_attr": False,
1281    }
1282
1283
1284class ColumnConstraint(Expression):
1285    arg_types = {"this": False, "kind": True}
1286
1287    @property
1288    def kind(self) -> ColumnConstraintKind:
1289        return self.args["kind"]
1290
1291
1292class ColumnConstraintKind(Expression):
1293    pass
1294
1295
1296class AutoIncrementColumnConstraint(ColumnConstraintKind):
1297    pass
1298
1299
1300class CaseSpecificColumnConstraint(ColumnConstraintKind):
1301    arg_types = {"not_": True}
1302
1303
1304class CharacterSetColumnConstraint(ColumnConstraintKind):
1305    arg_types = {"this": True}
1306
1307
1308class CheckColumnConstraint(ColumnConstraintKind):
1309    pass
1310
1311
1312class ClusteredColumnConstraint(ColumnConstraintKind):
1313    pass
1314
1315
1316class CollateColumnConstraint(ColumnConstraintKind):
1317    pass
1318
1319
1320class CommentColumnConstraint(ColumnConstraintKind):
1321    pass
1322
1323
1324class CompressColumnConstraint(ColumnConstraintKind):
1325    pass
1326
1327
1328class DateFormatColumnConstraint(ColumnConstraintKind):
1329    arg_types = {"this": True}
1330
1331
1332class DefaultColumnConstraint(ColumnConstraintKind):
1333    pass
1334
1335
1336class EncodeColumnConstraint(ColumnConstraintKind):
1337    pass
1338
1339
1340class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
1341    # this: True -> ALWAYS, this: False -> BY DEFAULT
1342    arg_types = {
1343        "this": False,
1344        "expression": False,
1345        "on_null": False,
1346        "start": False,
1347        "increment": False,
1348        "minvalue": False,
1349        "maxvalue": False,
1350        "cycle": False,
1351    }
1352
1353
1354# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1355class IndexColumnConstraint(ColumnConstraintKind):
1356    arg_types = {
1357        "this": False,
1358        "schema": True,
1359        "kind": False,
1360        "index_type": False,
1361        "options": False,
1362    }
1363
1364
1365class InlineLengthColumnConstraint(ColumnConstraintKind):
1366    pass
1367
1368
1369class NonClusteredColumnConstraint(ColumnConstraintKind):
1370    pass
1371
1372
1373class NotForReplicationColumnConstraint(ColumnConstraintKind):
1374    arg_types = {}
1375
1376
1377class NotNullColumnConstraint(ColumnConstraintKind):
1378    arg_types = {"allow_null": False}
1379
1380
1381# https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
1382class OnUpdateColumnConstraint(ColumnConstraintKind):
1383    pass
1384
1385
1386class PrimaryKeyColumnConstraint(ColumnConstraintKind):
1387    arg_types = {"desc": False}
1388
1389
1390class TitleColumnConstraint(ColumnConstraintKind):
1391    pass
1392
1393
1394class UniqueColumnConstraint(ColumnConstraintKind):
1395    arg_types = {"this": False, "index_type": False}
1396
1397
1398class UppercaseColumnConstraint(ColumnConstraintKind):
1399    arg_types: t.Dict[str, t.Any] = {}
1400
1401
1402class PathColumnConstraint(ColumnConstraintKind):
1403    pass
1404
1405
1406# computed column expression
1407# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16
1408class ComputedColumnConstraint(ColumnConstraintKind):
1409    arg_types = {"this": True, "persisted": False, "not_null": False}
1410
1411
1412class Constraint(Expression):
1413    arg_types = {"this": True, "expressions": True}
1414
1415
1416class Delete(Expression):
1417    arg_types = {
1418        "with": False,
1419        "this": False,
1420        "using": False,
1421        "where": False,
1422        "returning": False,
1423        "limit": False,
1424        "tables": False,  # Multiple-Table Syntax (MySQL)
1425    }
1426
1427    def delete(
1428        self,
1429        table: ExpOrStr,
1430        dialect: DialectType = None,
1431        copy: bool = True,
1432        **opts,
1433    ) -> Delete:
1434        """
1435        Create a DELETE expression or replace the table on an existing DELETE expression.
1436
1437        Example:
1438            >>> delete("tbl").sql()
1439            'DELETE FROM tbl'
1440
1441        Args:
1442            table: the table from which to delete.
1443            dialect: the dialect used to parse the input expression.
1444            copy: if `False`, modify this expression instance in-place.
1445            opts: other options to use to parse the input expressions.
1446
1447        Returns:
1448            Delete: the modified expression.
1449        """
1450        return _apply_builder(
1451            expression=table,
1452            instance=self,
1453            arg="this",
1454            dialect=dialect,
1455            into=Table,
1456            copy=copy,
1457            **opts,
1458        )
1459
1460    def where(
1461        self,
1462        *expressions: t.Optional[ExpOrStr],
1463        append: bool = True,
1464        dialect: DialectType = None,
1465        copy: bool = True,
1466        **opts,
1467    ) -> Delete:
1468        """
1469        Append to or set the WHERE expressions.
1470
1471        Example:
1472            >>> delete("tbl").where("x = 'a' OR x < 'b'").sql()
1473            "DELETE FROM tbl WHERE x = 'a' OR x < 'b'"
1474
1475        Args:
1476            *expressions: the SQL code strings to parse.
1477                If an `Expression` instance is passed, it will be used as-is.
1478                Multiple expressions are combined with an AND operator.
1479            append: if `True`, AND the new expressions to any existing expression.
1480                Otherwise, this resets the expression.
1481            dialect: the dialect used to parse the input expressions.
1482            copy: if `False`, modify this expression instance in-place.
1483            opts: other options to use to parse the input expressions.
1484
1485        Returns:
1486            Delete: the modified expression.
1487        """
1488        return _apply_conjunction_builder(
1489            *expressions,
1490            instance=self,
1491            arg="where",
1492            append=append,
1493            into=Where,
1494            dialect=dialect,
1495            copy=copy,
1496            **opts,
1497        )
1498
1499    def returning(
1500        self,
1501        expression: ExpOrStr,
1502        dialect: DialectType = None,
1503        copy: bool = True,
1504        **opts,
1505    ) -> Delete:
1506        """
1507        Set the RETURNING expression. Not supported by all dialects.
1508
1509        Example:
1510            >>> delete("tbl").returning("*", dialect="postgres").sql()
1511            'DELETE FROM tbl RETURNING *'
1512
1513        Args:
1514            expression: the SQL code strings to parse.
1515                If an `Expression` instance is passed, it will be used as-is.
1516            dialect: the dialect used to parse the input expressions.
1517            copy: if `False`, modify this expression instance in-place.
1518            opts: other options to use to parse the input expressions.
1519
1520        Returns:
1521            Delete: the modified expression.
1522        """
1523        return _apply_builder(
1524            expression=expression,
1525            instance=self,
1526            arg="returning",
1527            prefix="RETURNING",
1528            dialect=dialect,
1529            copy=copy,
1530            into=Returning,
1531            **opts,
1532        )
1533
1534
1535class Drop(Expression):
1536    arg_types = {
1537        "this": False,
1538        "kind": False,
1539        "exists": False,
1540        "temporary": False,
1541        "materialized": False,
1542        "cascade": False,
1543        "constraints": False,
1544        "purge": False,
1545    }
1546
1547
1548class Filter(Expression):
1549    arg_types = {"this": True, "expression": True}
1550
1551
1552class Check(Expression):
1553    pass
1554
1555
1556# https://docs.snowflake.com/en/sql-reference/constructs/connect-by
1557class Connect(Expression):
1558    arg_types = {"start": False, "connect": True}
1559
1560
1561class Prior(Expression):
1562    pass
1563
1564
1565class Directory(Expression):
1566    # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html
1567    arg_types = {"this": True, "local": False, "row_format": False}
1568
1569
1570class ForeignKey(Expression):
1571    arg_types = {
1572        "expressions": True,
1573        "reference": False,
1574        "delete": False,
1575        "update": False,
1576    }
1577
1578
1579class ColumnPrefix(Expression):
1580    arg_types = {"this": True, "expression": True}
1581
1582
1583class PrimaryKey(Expression):
1584    arg_types = {"expressions": True, "options": False}
1585
1586
1587# https://www.postgresql.org/docs/9.1/sql-selectinto.html
1588# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples
1589class Into(Expression):
1590    arg_types = {"this": True, "temporary": False, "unlogged": False}
1591
1592
1593class From(Expression):
1594    @property
1595    def name(self) -> str:
1596        return self.this.name
1597
1598    @property
1599    def alias_or_name(self) -> str:
1600        return self.this.alias_or_name
1601
1602
1603class Having(Expression):
1604    pass
1605
1606
1607class Hint(Expression):
1608    arg_types = {"expressions": True}
1609
1610
1611class JoinHint(Expression):
1612    arg_types = {"this": True, "expressions": True}
1613
1614
1615class Identifier(Expression):
1616    arg_types = {"this": True, "quoted": False, "global": False, "temporary": False}
1617
1618    @property
1619    def quoted(self) -> bool:
1620        return bool(self.args.get("quoted"))
1621
1622    @property
1623    def hashable_args(self) -> t.Any:
1624        return (self.this, self.quoted)
1625
1626    @property
1627    def output_name(self) -> str:
1628        return self.name
1629
1630
1631# https://www.postgresql.org/docs/current/indexes-opclass.html
1632class Opclass(Expression):
1633    arg_types = {"this": True, "expression": True}
1634
1635
1636class Index(Expression):
1637    arg_types = {
1638        "this": False,
1639        "table": False,
1640        "using": False,
1641        "where": False,
1642        "columns": False,
1643        "unique": False,
1644        "primary": False,
1645        "amp": False,  # teradata
1646        "partition_by": False,  # teradata
1647        "where": False,  # postgres partial indexes
1648    }
1649
1650
1651class Insert(DDL):
1652    arg_types = {
1653        "with": False,
1654        "this": True,
1655        "expression": False,
1656        "conflict": False,
1657        "returning": False,
1658        "overwrite": False,
1659        "exists": False,
1660        "partition": False,
1661        "alternative": False,
1662        "where": False,
1663        "ignore": False,
1664        "by_name": False,
1665    }
1666
1667    def with_(
1668        self,
1669        alias: ExpOrStr,
1670        as_: ExpOrStr,
1671        recursive: t.Optional[bool] = None,
1672        append: bool = True,
1673        dialect: DialectType = None,
1674        copy: bool = True,
1675        **opts,
1676    ) -> Insert:
1677        """
1678        Append to or set the common table expressions.
1679
1680        Example:
1681            >>> insert("SELECT x FROM cte", "t").with_("cte", as_="SELECT * FROM tbl").sql()
1682            'WITH cte AS (SELECT * FROM tbl) INSERT INTO t SELECT x FROM cte'
1683
1684        Args:
1685            alias: the SQL code string to parse as the table name.
1686                If an `Expression` instance is passed, this is used as-is.
1687            as_: the SQL code string to parse as the table expression.
1688                If an `Expression` instance is passed, it will be used as-is.
1689            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
1690            append: if `True`, add to any existing expressions.
1691                Otherwise, this resets the expressions.
1692            dialect: the dialect used to parse the input expression.
1693            copy: if `False`, modify this expression instance in-place.
1694            opts: other options to use to parse the input expressions.
1695
1696        Returns:
1697            The modified expression.
1698        """
1699        return _apply_cte_builder(
1700            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
1701        )
1702
1703
1704class OnConflict(Expression):
1705    arg_types = {
1706        "duplicate": False,
1707        "expressions": False,
1708        "nothing": False,
1709        "key": False,
1710        "constraint": False,
1711    }
1712
1713
1714class Returning(Expression):
1715    arg_types = {"expressions": True, "into": False}
1716
1717
1718# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
1719class Introducer(Expression):
1720    arg_types = {"this": True, "expression": True}
1721
1722
1723# national char, like n'utf8'
1724class National(Expression):
1725    pass
1726
1727
1728class LoadData(Expression):
1729    arg_types = {
1730        "this": True,
1731        "local": False,
1732        "overwrite": False,
1733        "inpath": True,
1734        "partition": False,
1735        "input_format": False,
1736        "serde": False,
1737    }
1738
1739
1740class Partition(Expression):
1741    arg_types = {"expressions": True}
1742
1743
1744class Fetch(Expression):
1745    arg_types = {
1746        "direction": False,
1747        "count": False,
1748        "percent": False,
1749        "with_ties": False,
1750    }
1751
1752
1753class Group(Expression):
1754    arg_types = {
1755        "expressions": False,
1756        "grouping_sets": False,
1757        "cube": False,
1758        "rollup": False,
1759        "totals": False,
1760        "all": False,
1761    }
1762
1763
1764class Lambda(Expression):
1765    arg_types = {"this": True, "expressions": True}
1766
1767
1768class Limit(Expression):
1769    arg_types = {"this": False, "expression": True, "offset": False}
1770
1771
1772class Literal(Condition):
1773    arg_types = {"this": True, "is_string": True}
1774
1775    @property
1776    def hashable_args(self) -> t.Any:
1777        return (self.this, self.args.get("is_string"))
1778
1779    @classmethod
1780    def number(cls, number) -> Literal:
1781        return cls(this=str(number), is_string=False)
1782
1783    @classmethod
1784    def string(cls, string) -> Literal:
1785        return cls(this=str(string), is_string=True)
1786
1787    @property
1788    def output_name(self) -> str:
1789        return self.name
1790
1791
1792class Join(Expression):
1793    arg_types = {
1794        "this": True,
1795        "on": False,
1796        "side": False,
1797        "kind": False,
1798        "using": False,
1799        "method": False,
1800        "global": False,
1801        "hint": False,
1802    }
1803
1804    @property
1805    def method(self) -> str:
1806        return self.text("method").upper()
1807
1808    @property
1809    def kind(self) -> str:
1810        return self.text("kind").upper()
1811
1812    @property
1813    def side(self) -> str:
1814        return self.text("side").upper()
1815
1816    @property
1817    def hint(self) -> str:
1818        return self.text("hint").upper()
1819
1820    @property
1821    def alias_or_name(self) -> str:
1822        return self.this.alias_or_name
1823
1824    def on(
1825        self,
1826        *expressions: t.Optional[ExpOrStr],
1827        append: bool = True,
1828        dialect: DialectType = None,
1829        copy: bool = True,
1830        **opts,
1831    ) -> Join:
1832        """
1833        Append to or set the ON expressions.
1834
1835        Example:
1836            >>> import sqlglot
1837            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
1838            'JOIN x ON y = 1'
1839
1840        Args:
1841            *expressions: the SQL code strings to parse.
1842                If an `Expression` instance is passed, it will be used as-is.
1843                Multiple expressions are combined with an AND operator.
1844            append: if `True`, AND the new expressions to any existing expression.
1845                Otherwise, this resets the expression.
1846            dialect: the dialect used to parse the input expressions.
1847            copy: if `False`, modify this expression instance in-place.
1848            opts: other options to use to parse the input expressions.
1849
1850        Returns:
1851            The modified Join expression.
1852        """
1853        join = _apply_conjunction_builder(
1854            *expressions,
1855            instance=self,
1856            arg="on",
1857            append=append,
1858            dialect=dialect,
1859            copy=copy,
1860            **opts,
1861        )
1862
1863        if join.kind == "CROSS":
1864            join.set("kind", None)
1865
1866        return join
1867
1868    def using(
1869        self,
1870        *expressions: t.Optional[ExpOrStr],
1871        append: bool = True,
1872        dialect: DialectType = None,
1873        copy: bool = True,
1874        **opts,
1875    ) -> Join:
1876        """
1877        Append to or set the USING expressions.
1878
1879        Example:
1880            >>> import sqlglot
1881            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
1882            'JOIN x USING (foo, bla)'
1883
1884        Args:
1885            *expressions: the SQL code strings to parse.
1886                If an `Expression` instance is passed, it will be used as-is.
1887            append: if `True`, concatenate the new expressions to the existing "using" list.
1888                Otherwise, this resets the expression.
1889            dialect: the dialect used to parse the input expressions.
1890            copy: if `False`, modify this expression instance in-place.
1891            opts: other options to use to parse the input expressions.
1892
1893        Returns:
1894            The modified Join expression.
1895        """
1896        join = _apply_list_builder(
1897            *expressions,
1898            instance=self,
1899            arg="using",
1900            append=append,
1901            dialect=dialect,
1902            copy=copy,
1903            **opts,
1904        )
1905
1906        if join.kind == "CROSS":
1907            join.set("kind", None)
1908
1909        return join
1910
1911
1912class Lateral(UDTF):
1913    arg_types = {"this": True, "view": False, "outer": False, "alias": False}
1914
1915
1916class MatchRecognize(Expression):
1917    arg_types = {
1918        "partition_by": False,
1919        "order": False,
1920        "measures": False,
1921        "rows": False,
1922        "after": False,
1923        "pattern": False,
1924        "define": False,
1925        "alias": False,
1926    }
1927
1928
1929# Clickhouse FROM FINAL modifier
1930# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
1931class Final(Expression):
1932    pass
1933
1934
1935class Offset(Expression):
1936    arg_types = {"this": False, "expression": True}
1937
1938
1939class Order(Expression):
1940    arg_types = {"this": False, "expressions": True}
1941
1942
1943# hive specific sorts
1944# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
1945class Cluster(Order):
1946    pass
1947
1948
1949class Distribute(Order):
1950    pass
1951
1952
1953class Sort(Order):
1954    pass
1955
1956
1957class Ordered(Expression):
1958    arg_types = {"this": True, "desc": False, "nulls_first": True}
1959
1960
1961class Property(Expression):
1962    arg_types = {"this": True, "value": True}
1963
1964
1965class AlgorithmProperty(Property):
1966    arg_types = {"this": True}
1967
1968
1969class AutoIncrementProperty(Property):
1970    arg_types = {"this": True}
1971
1972
1973class BlockCompressionProperty(Property):
1974    arg_types = {"autotemp": False, "always": False, "default": True, "manual": True, "never": True}
1975
1976
1977class CharacterSetProperty(Property):
1978    arg_types = {"this": True, "default": True}
1979
1980
1981class ChecksumProperty(Property):
1982    arg_types = {"on": False, "default": False}
1983
1984
1985class CollateProperty(Property):
1986    arg_types = {"this": True, "default": False}
1987
1988
1989class CopyGrantsProperty(Property):
1990    arg_types = {}
1991
1992
1993class DataBlocksizeProperty(Property):
1994    arg_types = {
1995        "size": False,
1996        "units": False,
1997        "minimum": False,
1998        "maximum": False,
1999        "default": False,
2000    }
2001
2002
2003class DefinerProperty(Property):
2004    arg_types = {"this": True}
2005
2006
2007class DistKeyProperty(Property):
2008    arg_types = {"this": True}
2009
2010
2011class DistStyleProperty(Property):
2012    arg_types = {"this": True}
2013
2014
2015class EngineProperty(Property):
2016    arg_types = {"this": True}
2017
2018
2019class HeapProperty(Property):
2020    arg_types = {}
2021
2022
2023class ToTableProperty(Property):
2024    arg_types = {"this": True}
2025
2026
2027class ExecuteAsProperty(Property):
2028    arg_types = {"this": True}
2029
2030
2031class ExternalProperty(Property):
2032    arg_types = {"this": False}
2033
2034
2035class FallbackProperty(Property):
2036    arg_types = {"no": True, "protection": False}
2037
2038
2039class FileFormatProperty(Property):
2040    arg_types = {"this": True}
2041
2042
2043class FreespaceProperty(Property):
2044    arg_types = {"this": True, "percent": False}
2045
2046
2047class InputModelProperty(Property):
2048    arg_types = {"this": True}
2049
2050
2051class OutputModelProperty(Property):
2052    arg_types = {"this": True}
2053
2054
2055class IsolatedLoadingProperty(Property):
2056    arg_types = {
2057        "no": True,
2058        "concurrent": True,
2059        "for_all": True,
2060        "for_insert": True,
2061        "for_none": True,
2062    }
2063
2064
2065class JournalProperty(Property):
2066    arg_types = {
2067        "no": False,
2068        "dual": False,
2069        "before": False,
2070        "local": False,
2071        "after": False,
2072    }
2073
2074
2075class LanguageProperty(Property):
2076    arg_types = {"this": True}
2077
2078
2079# spark ddl
2080class ClusteredByProperty(Property):
2081    arg_types = {"expressions": True, "sorted_by": False, "buckets": True}
2082
2083
2084class DictProperty(Property):
2085    arg_types = {"this": True, "kind": True, "settings": False}
2086
2087
2088class DictSubProperty(Property):
2089    pass
2090
2091
2092class DictRange(Property):
2093    arg_types = {"this": True, "min": True, "max": True}
2094
2095
2096# Clickhouse CREATE ... ON CLUSTER modifier
2097# https://clickhouse.com/docs/en/sql-reference/distributed-ddl
2098class OnCluster(Property):
2099    arg_types = {"this": True}
2100
2101
2102class LikeProperty(Property):
2103    arg_types = {"this": True, "expressions": False}
2104
2105
2106class LocationProperty(Property):
2107    arg_types = {"this": True}
2108
2109
2110class LockingProperty(Property):
2111    arg_types = {
2112        "this": False,
2113        "kind": True,
2114        "for_or_in": False,
2115        "lock_type": True,
2116        "override": False,
2117    }
2118
2119
2120class LogProperty(Property):
2121    arg_types = {"no": True}
2122
2123
2124class MaterializedProperty(Property):
2125    arg_types = {"this": False}
2126
2127
2128class MergeBlockRatioProperty(Property):
2129    arg_types = {"this": False, "no": False, "default": False, "percent": False}
2130
2131
2132class NoPrimaryIndexProperty(Property):
2133    arg_types = {}
2134
2135
2136class OnProperty(Property):
2137    arg_types = {"this": True}
2138
2139
2140class OnCommitProperty(Property):
2141    arg_types = {"delete": False}
2142
2143
2144class PartitionedByProperty(Property):
2145    arg_types = {"this": True}
2146
2147
2148class RemoteWithConnectionModelProperty(Property):
2149    arg_types = {"this": True}
2150
2151
2152class ReturnsProperty(Property):
2153    arg_types = {"this": True, "is_table": False, "table": False}
2154
2155
2156class RowFormatProperty(Property):
2157    arg_types = {"this": True}
2158
2159
2160class RowFormatDelimitedProperty(Property):
2161    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
2162    arg_types = {
2163        "fields": False,
2164        "escaped": False,
2165        "collection_items": False,
2166        "map_keys": False,
2167        "lines": False,
2168        "null": False,
2169        "serde": False,
2170    }
2171
2172
2173class RowFormatSerdeProperty(Property):
2174    arg_types = {"this": True, "serde_properties": False}
2175
2176
2177# https://spark.apache.org/docs/3.1.2/sql-ref-syntax-qry-select-transform.html
2178class QueryTransform(Expression):
2179    arg_types = {
2180        "expressions": True,
2181        "command_script": True,
2182        "schema": False,
2183        "row_format_before": False,
2184        "record_writer": False,
2185        "row_format_after": False,
2186        "record_reader": False,
2187    }
2188
2189
2190class SampleProperty(Property):
2191    arg_types = {"this": True}
2192
2193
2194class SchemaCommentProperty(Property):
2195    arg_types = {"this": True}
2196
2197
2198class SerdeProperties(Property):
2199    arg_types = {"expressions": True}
2200
2201
2202class SetProperty(Property):
2203    arg_types = {"multi": True}
2204
2205
2206class SettingsProperty(Property):
2207    arg_types = {"expressions": True}
2208
2209
2210class SortKeyProperty(Property):
2211    arg_types = {"this": True, "compound": False}
2212
2213
2214class SqlSecurityProperty(Property):
2215    arg_types = {"definer": True}
2216
2217
2218class StabilityProperty(Property):
2219    arg_types = {"this": True}
2220
2221
2222class TemporaryProperty(Property):
2223    arg_types = {}
2224
2225
2226class TransformModelProperty(Property):
2227    arg_types = {"expressions": True}
2228
2229
2230class TransientProperty(Property):
2231    arg_types = {"this": False}
2232
2233
2234class VolatileProperty(Property):
2235    arg_types = {"this": False}
2236
2237
2238class WithDataProperty(Property):
2239    arg_types = {"no": True, "statistics": False}
2240
2241
2242class WithJournalTableProperty(Property):
2243    arg_types = {"this": True}
2244
2245
2246class Properties(Expression):
2247    arg_types = {"expressions": True}
2248
2249    NAME_TO_PROPERTY = {
2250        "ALGORITHM": AlgorithmProperty,
2251        "AUTO_INCREMENT": AutoIncrementProperty,
2252        "CHARACTER SET": CharacterSetProperty,
2253        "CLUSTERED_BY": ClusteredByProperty,
2254        "COLLATE": CollateProperty,
2255        "COMMENT": SchemaCommentProperty,
2256        "DEFINER": DefinerProperty,
2257        "DISTKEY": DistKeyProperty,
2258        "DISTSTYLE": DistStyleProperty,
2259        "ENGINE": EngineProperty,
2260        "EXECUTE AS": ExecuteAsProperty,
2261        "FORMAT": FileFormatProperty,
2262        "LANGUAGE": LanguageProperty,
2263        "LOCATION": LocationProperty,
2264        "PARTITIONED_BY": PartitionedByProperty,
2265        "RETURNS": ReturnsProperty,
2266        "ROW_FORMAT": RowFormatProperty,
2267        "SORTKEY": SortKeyProperty,
2268    }
2269
2270    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
2271
2272    # CREATE property locations
2273    # Form: schema specified
2274    #   create [POST_CREATE]
2275    #     table a [POST_NAME]
2276    #     (b int) [POST_SCHEMA]
2277    #     with ([POST_WITH])
2278    #     index (b) [POST_INDEX]
2279    #
2280    # Form: alias selection
2281    #   create [POST_CREATE]
2282    #     table a [POST_NAME]
2283    #     as [POST_ALIAS] (select * from b) [POST_EXPRESSION]
2284    #     index (c) [POST_INDEX]
2285    class Location(AutoName):
2286        POST_CREATE = auto()
2287        POST_NAME = auto()
2288        POST_SCHEMA = auto()
2289        POST_WITH = auto()
2290        POST_ALIAS = auto()
2291        POST_EXPRESSION = auto()
2292        POST_INDEX = auto()
2293        UNSUPPORTED = auto()
2294
2295    @classmethod
2296    def from_dict(cls, properties_dict: t.Dict) -> Properties:
2297        expressions = []
2298        for key, value in properties_dict.items():
2299            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
2300            if property_cls:
2301                expressions.append(property_cls(this=convert(value)))
2302            else:
2303                expressions.append(Property(this=Literal.string(key), value=convert(value)))
2304
2305        return cls(expressions=expressions)
2306
2307
2308class Qualify(Expression):
2309    pass
2310
2311
2312class InputOutputFormat(Expression):
2313    arg_types = {"input_format": False, "output_format": False}
2314
2315
2316# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
2317class Return(Expression):
2318    pass
2319
2320
2321class Reference(Expression):
2322    arg_types = {"this": True, "expressions": False, "options": False}
2323
2324
2325class Tuple(Expression):
2326    arg_types = {"expressions": False}
2327
2328    def isin(
2329        self,
2330        *expressions: t.Any,
2331        query: t.Optional[ExpOrStr] = None,
2332        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
2333        copy: bool = True,
2334        **opts,
2335    ) -> In:
2336        return In(
2337            this=maybe_copy(self, copy),
2338            expressions=[convert(e, copy=copy) for e in expressions],
2339            query=maybe_parse(query, copy=copy, **opts) if query else None,
2340            unnest=Unnest(
2341                expressions=[
2342                    maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts) for e in ensure_list(unnest)
2343                ]
2344            )
2345            if unnest
2346            else None,
2347        )
2348
2349
2350class Subqueryable(Unionable):
2351    def subquery(self, alias: t.Optional[ExpOrStr] = None, copy: bool = True) -> Subquery:
2352        """
2353        Convert this expression to an aliased expression that can be used as a Subquery.
2354
2355        Example:
2356            >>> subquery = Select().select("x").from_("tbl").subquery()
2357            >>> Select().select("x").from_(subquery).sql()
2358            'SELECT x FROM (SELECT x FROM tbl)'
2359
2360        Args:
2361            alias (str | Identifier): an optional alias for the subquery
2362            copy (bool): if `False`, modify this expression instance in-place.
2363
2364        Returns:
2365            Alias: the subquery
2366        """
2367        instance = maybe_copy(self, copy)
2368        if not isinstance(alias, Expression):
2369            alias = TableAlias(this=to_identifier(alias)) if alias else None
2370
2371        return Subquery(this=instance, alias=alias)
2372
2373    def limit(
2374        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2375    ) -> Select:
2376        raise NotImplementedError
2377
2378    @property
2379    def ctes(self):
2380        with_ = self.args.get("with")
2381        if not with_:
2382            return []
2383        return with_.expressions
2384
2385    @property
2386    def selects(self) -> t.List[Expression]:
2387        raise NotImplementedError("Subqueryable objects must implement `selects`")
2388
2389    @property
2390    def named_selects(self) -> t.List[str]:
2391        raise NotImplementedError("Subqueryable objects must implement `named_selects`")
2392
2393    def select(
2394        self,
2395        *expressions: t.Optional[ExpOrStr],
2396        append: bool = True,
2397        dialect: DialectType = None,
2398        copy: bool = True,
2399        **opts,
2400    ) -> Subqueryable:
2401        raise NotImplementedError("Subqueryable objects must implement `select`")
2402
2403    def with_(
2404        self,
2405        alias: ExpOrStr,
2406        as_: ExpOrStr,
2407        recursive: t.Optional[bool] = None,
2408        append: bool = True,
2409        dialect: DialectType = None,
2410        copy: bool = True,
2411        **opts,
2412    ) -> Subqueryable:
2413        """
2414        Append to or set the common table expressions.
2415
2416        Example:
2417            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
2418            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
2419
2420        Args:
2421            alias: the SQL code string to parse as the table name.
2422                If an `Expression` instance is passed, this is used as-is.
2423            as_: the SQL code string to parse as the table expression.
2424                If an `Expression` instance is passed, it will be used as-is.
2425            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
2426            append: if `True`, add to any existing expressions.
2427                Otherwise, this resets the expressions.
2428            dialect: the dialect used to parse the input expression.
2429            copy: if `False`, modify this expression instance in-place.
2430            opts: other options to use to parse the input expressions.
2431
2432        Returns:
2433            The modified expression.
2434        """
2435        return _apply_cte_builder(
2436            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
2437        )
2438
2439
2440QUERY_MODIFIERS = {
2441    "match": False,
2442    "laterals": False,
2443    "joins": False,
2444    "connect": False,
2445    "pivots": False,
2446    "where": False,
2447    "group": False,
2448    "having": False,
2449    "qualify": False,
2450    "windows": False,
2451    "distribute": False,
2452    "sort": False,
2453    "cluster": False,
2454    "order": False,
2455    "limit": False,
2456    "offset": False,
2457    "locks": False,
2458    "sample": False,
2459    "settings": False,
2460    "format": False,
2461}
2462
2463
2464# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver16
2465class WithTableHint(Expression):
2466    arg_types = {"expressions": True}
2467
2468
2469# https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
2470class IndexTableHint(Expression):
2471    arg_types = {"this": True, "expressions": False, "target": False}
2472
2473
2474class Table(Expression):
2475    arg_types = {
2476        "this": True,
2477        "alias": False,
2478        "db": False,
2479        "catalog": False,
2480        "laterals": False,
2481        "joins": False,
2482        "pivots": False,
2483        "hints": False,
2484        "system_time": False,
2485        "version": False,
2486        "format": False,
2487        "pattern": False,
2488        "index": False,
2489    }
2490
2491    @property
2492    def name(self) -> str:
2493        if isinstance(self.this, Func):
2494            return ""
2495        return self.this.name
2496
2497    @property
2498    def db(self) -> str:
2499        return self.text("db")
2500
2501    @property
2502    def catalog(self) -> str:
2503        return self.text("catalog")
2504
2505    @property
2506    def selects(self) -> t.List[Expression]:
2507        return []
2508
2509    @property
2510    def named_selects(self) -> t.List[str]:
2511        return []
2512
2513    @property
2514    def parts(self) -> t.List[Expression]:
2515        """Return the parts of a table in order catalog, db, table."""
2516        parts: t.List[Expression] = []
2517
2518        for arg in ("catalog", "db", "this"):
2519            part = self.args.get(arg)
2520
2521            if isinstance(part, Dot):
2522                parts.extend(part.flatten())
2523            elif isinstance(part, Expression):
2524                parts.append(part)
2525
2526        return parts
2527
2528
2529class Union(Subqueryable):
2530    arg_types = {
2531        "with": False,
2532        "this": True,
2533        "expression": True,
2534        "distinct": False,
2535        "by_name": False,
2536        **QUERY_MODIFIERS,
2537    }
2538
2539    def limit(
2540        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2541    ) -> Select:
2542        """
2543        Set the LIMIT expression.
2544
2545        Example:
2546            >>> select("1").union(select("1")).limit(1).sql()
2547            'SELECT * FROM (SELECT 1 UNION SELECT 1) AS _l_0 LIMIT 1'
2548
2549        Args:
2550            expression: the SQL code string to parse.
2551                This can also be an integer.
2552                If a `Limit` instance is passed, this is used as-is.
2553                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2554            dialect: the dialect used to parse the input expression.
2555            copy: if `False`, modify this expression instance in-place.
2556            opts: other options to use to parse the input expressions.
2557
2558        Returns:
2559            The limited subqueryable.
2560        """
2561        return (
2562            select("*")
2563            .from_(self.subquery(alias="_l_0", copy=copy))
2564            .limit(expression, dialect=dialect, copy=False, **opts)
2565        )
2566
2567    def select(
2568        self,
2569        *expressions: t.Optional[ExpOrStr],
2570        append: bool = True,
2571        dialect: DialectType = None,
2572        copy: bool = True,
2573        **opts,
2574    ) -> Union:
2575        """Append to or set the SELECT of the union recursively.
2576
2577        Example:
2578            >>> from sqlglot import parse_one
2579            >>> parse_one("select a from x union select a from y union select a from z").select("b").sql()
2580            'SELECT a, b FROM x UNION SELECT a, b FROM y UNION SELECT a, b FROM z'
2581
2582        Args:
2583            *expressions: the SQL code strings to parse.
2584                If an `Expression` instance is passed, it will be used as-is.
2585            append: if `True`, add to any existing expressions.
2586                Otherwise, this resets the expressions.
2587            dialect: the dialect used to parse the input expressions.
2588            copy: if `False`, modify this expression instance in-place.
2589            opts: other options to use to parse the input expressions.
2590
2591        Returns:
2592            Union: the modified expression.
2593        """
2594        this = self.copy() if copy else self
2595        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
2596        this.expression.unnest().select(
2597            *expressions, append=append, dialect=dialect, copy=False, **opts
2598        )
2599        return this
2600
2601    @property
2602    def named_selects(self) -> t.List[str]:
2603        return self.this.unnest().named_selects
2604
2605    @property
2606    def is_star(self) -> bool:
2607        return self.this.is_star or self.expression.is_star
2608
2609    @property
2610    def selects(self) -> t.List[Expression]:
2611        return self.this.unnest().selects
2612
2613    @property
2614    def left(self) -> Expression:
2615        return self.this
2616
2617    @property
2618    def right(self) -> Expression:
2619        return self.expression
2620
2621
2622class Except(Union):
2623    pass
2624
2625
2626class Intersect(Union):
2627    pass
2628
2629
2630class Unnest(UDTF):
2631    arg_types = {
2632        "expressions": True,
2633        "alias": False,
2634        "offset": False,
2635    }
2636
2637
2638class Update(Expression):
2639    arg_types = {
2640        "with": False,
2641        "this": False,
2642        "expressions": True,
2643        "from": False,
2644        "where": False,
2645        "returning": False,
2646        "order": False,
2647        "limit": False,
2648    }
2649
2650
2651class Values(UDTF):
2652    arg_types = {
2653        "expressions": True,
2654        "ordinality": False,
2655        "alias": False,
2656    }
2657
2658
2659class Var(Expression):
2660    pass
2661
2662
2663class Version(Expression):
2664    """
2665    Time travel, iceberg, bigquery etc
2666    https://trino.io/docs/current/connector/iceberg.html?highlight=snapshot#using-snapshots
2667    https://www.databricks.com/blog/2019/02/04/introducing-delta-time-travel-for-large-scale-data-lakes.html
2668    https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#for_system_time_as_of
2669    https://learn.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-ver16
2670    this is either TIMESTAMP or VERSION
2671    kind is ("AS OF", "BETWEEN")
2672    """
2673
2674    arg_types = {"this": True, "kind": True, "expression": False}
2675
2676
2677class Schema(Expression):
2678    arg_types = {"this": False, "expressions": False}
2679
2680
2681# https://dev.mysql.com/doc/refman/8.0/en/select.html
2682# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html
2683class Lock(Expression):
2684    arg_types = {"update": True, "expressions": False, "wait": False}
2685
2686
2687class Select(Subqueryable):
2688    arg_types = {
2689        "with": False,
2690        "kind": False,
2691        "expressions": False,
2692        "hint": False,
2693        "distinct": False,
2694        "into": False,
2695        "from": False,
2696        **QUERY_MODIFIERS,
2697    }
2698
2699    def from_(
2700        self, expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
2701    ) -> Select:
2702        """
2703        Set the FROM expression.
2704
2705        Example:
2706            >>> Select().from_("tbl").select("x").sql()
2707            'SELECT x FROM tbl'
2708
2709        Args:
2710            expression : the SQL code strings to parse.
2711                If a `From` instance is passed, this is used as-is.
2712                If another `Expression` instance is passed, it will be wrapped in a `From`.
2713            dialect: the dialect used to parse the input expression.
2714            copy: if `False`, modify this expression instance in-place.
2715            opts: other options to use to parse the input expressions.
2716
2717        Returns:
2718            The modified Select expression.
2719        """
2720        return _apply_builder(
2721            expression=expression,
2722            instance=self,
2723            arg="from",
2724            into=From,
2725            prefix="FROM",
2726            dialect=dialect,
2727            copy=copy,
2728            **opts,
2729        )
2730
2731    def group_by(
2732        self,
2733        *expressions: t.Optional[ExpOrStr],
2734        append: bool = True,
2735        dialect: DialectType = None,
2736        copy: bool = True,
2737        **opts,
2738    ) -> Select:
2739        """
2740        Set the GROUP BY expression.
2741
2742        Example:
2743            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
2744            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
2745
2746        Args:
2747            *expressions: the SQL code strings to parse.
2748                If a `Group` instance is passed, this is used as-is.
2749                If another `Expression` instance is passed, it will be wrapped in a `Group`.
2750                If nothing is passed in then a group by is not applied to the expression
2751            append: if `True`, add to any existing expressions.
2752                Otherwise, this flattens all the `Group` expression into a single expression.
2753            dialect: the dialect used to parse the input expression.
2754            copy: if `False`, modify this expression instance in-place.
2755            opts: other options to use to parse the input expressions.
2756
2757        Returns:
2758            The modified Select expression.
2759        """
2760        if not expressions:
2761            return self if not copy else self.copy()
2762
2763        return _apply_child_list_builder(
2764            *expressions,
2765            instance=self,
2766            arg="group",
2767            append=append,
2768            copy=copy,
2769            prefix="GROUP BY",
2770            into=Group,
2771            dialect=dialect,
2772            **opts,
2773        )
2774
2775    def order_by(
2776        self,
2777        *expressions: t.Optional[ExpOrStr],
2778        append: bool = True,
2779        dialect: DialectType = None,
2780        copy: bool = True,
2781        **opts,
2782    ) -> Select:
2783        """
2784        Set the ORDER BY expression.
2785
2786        Example:
2787            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
2788            'SELECT x FROM tbl ORDER BY x DESC'
2789
2790        Args:
2791            *expressions: the SQL code strings to parse.
2792                If a `Group` instance is passed, this is used as-is.
2793                If another `Expression` instance is passed, it will be wrapped in a `Order`.
2794            append: if `True`, add to any existing expressions.
2795                Otherwise, this flattens all the `Order` expression into a single expression.
2796            dialect: the dialect used to parse the input expression.
2797            copy: if `False`, modify this expression instance in-place.
2798            opts: other options to use to parse the input expressions.
2799
2800        Returns:
2801            The modified Select expression.
2802        """
2803        return _apply_child_list_builder(
2804            *expressions,
2805            instance=self,
2806            arg="order",
2807            append=append,
2808            copy=copy,
2809            prefix="ORDER BY",
2810            into=Order,
2811            dialect=dialect,
2812            **opts,
2813        )
2814
2815    def sort_by(
2816        self,
2817        *expressions: t.Optional[ExpOrStr],
2818        append: bool = True,
2819        dialect: DialectType = None,
2820        copy: bool = True,
2821        **opts,
2822    ) -> Select:
2823        """
2824        Set the SORT BY expression.
2825
2826        Example:
2827            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql(dialect="hive")
2828            'SELECT x FROM tbl SORT BY x DESC'
2829
2830        Args:
2831            *expressions: the SQL code strings to parse.
2832                If a `Group` instance is passed, this is used as-is.
2833                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
2834            append: if `True`, add to any existing expressions.
2835                Otherwise, this flattens all the `Order` expression into a single expression.
2836            dialect: the dialect used to parse the input expression.
2837            copy: if `False`, modify this expression instance in-place.
2838            opts: other options to use to parse the input expressions.
2839
2840        Returns:
2841            The modified Select expression.
2842        """
2843        return _apply_child_list_builder(
2844            *expressions,
2845            instance=self,
2846            arg="sort",
2847            append=append,
2848            copy=copy,
2849            prefix="SORT BY",
2850            into=Sort,
2851            dialect=dialect,
2852            **opts,
2853        )
2854
2855    def cluster_by(
2856        self,
2857        *expressions: t.Optional[ExpOrStr],
2858        append: bool = True,
2859        dialect: DialectType = None,
2860        copy: bool = True,
2861        **opts,
2862    ) -> Select:
2863        """
2864        Set the CLUSTER BY expression.
2865
2866        Example:
2867            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql(dialect="hive")
2868            'SELECT x FROM tbl CLUSTER BY x DESC'
2869
2870        Args:
2871            *expressions: the SQL code strings to parse.
2872                If a `Group` instance is passed, this is used as-is.
2873                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
2874            append: if `True`, add to any existing expressions.
2875                Otherwise, this flattens all the `Order` expression into a single expression.
2876            dialect: the dialect used to parse the input expression.
2877            copy: if `False`, modify this expression instance in-place.
2878            opts: other options to use to parse the input expressions.
2879
2880        Returns:
2881            The modified Select expression.
2882        """
2883        return _apply_child_list_builder(
2884            *expressions,
2885            instance=self,
2886            arg="cluster",
2887            append=append,
2888            copy=copy,
2889            prefix="CLUSTER BY",
2890            into=Cluster,
2891            dialect=dialect,
2892            **opts,
2893        )
2894
2895    def limit(
2896        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2897    ) -> Select:
2898        """
2899        Set the LIMIT expression.
2900
2901        Example:
2902            >>> Select().from_("tbl").select("x").limit(10).sql()
2903            'SELECT x FROM tbl LIMIT 10'
2904
2905        Args:
2906            expression: the SQL code string to parse.
2907                This can also be an integer.
2908                If a `Limit` instance is passed, this is used as-is.
2909                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2910            dialect: the dialect used to parse the input expression.
2911            copy: if `False`, modify this expression instance in-place.
2912            opts: other options to use to parse the input expressions.
2913
2914        Returns:
2915            Select: the modified expression.
2916        """
2917        return _apply_builder(
2918            expression=expression,
2919            instance=self,
2920            arg="limit",
2921            into=Limit,
2922            prefix="LIMIT",
2923            dialect=dialect,
2924            copy=copy,
2925            into_arg="expression",
2926            **opts,
2927        )
2928
2929    def offset(
2930        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2931    ) -> Select:
2932        """
2933        Set the OFFSET expression.
2934
2935        Example:
2936            >>> Select().from_("tbl").select("x").offset(10).sql()
2937            'SELECT x FROM tbl OFFSET 10'
2938
2939        Args:
2940            expression: the SQL code string to parse.
2941                This can also be an integer.
2942                If a `Offset` instance is passed, this is used as-is.
2943                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
2944            dialect: the dialect used to parse the input expression.
2945            copy: if `False`, modify this expression instance in-place.
2946            opts: other options to use to parse the input expressions.
2947
2948        Returns:
2949            The modified Select expression.
2950        """
2951        return _apply_builder(
2952            expression=expression,
2953            instance=self,
2954            arg="offset",
2955            into=Offset,
2956            prefix="OFFSET",
2957            dialect=dialect,
2958            copy=copy,
2959            into_arg="expression",
2960            **opts,
2961        )
2962
2963    def select(
2964        self,
2965        *expressions: t.Optional[ExpOrStr],
2966        append: bool = True,
2967        dialect: DialectType = None,
2968        copy: bool = True,
2969        **opts,
2970    ) -> Select:
2971        """
2972        Append to or set the SELECT expressions.
2973
2974        Example:
2975            >>> Select().select("x", "y").sql()
2976            'SELECT x, y'
2977
2978        Args:
2979            *expressions: the SQL code strings to parse.
2980                If an `Expression` instance is passed, it will be used as-is.
2981            append: if `True`, add to any existing expressions.
2982                Otherwise, this resets the expressions.
2983            dialect: the dialect used to parse the input expressions.
2984            copy: if `False`, modify this expression instance in-place.
2985            opts: other options to use to parse the input expressions.
2986
2987        Returns:
2988            The modified Select expression.
2989        """
2990        return _apply_list_builder(
2991            *expressions,
2992            instance=self,
2993            arg="expressions",
2994            append=append,
2995            dialect=dialect,
2996            copy=copy,
2997            **opts,
2998        )
2999
3000    def lateral(
3001        self,
3002        *expressions: t.Optional[ExpOrStr],
3003        append: bool = True,
3004        dialect: DialectType = None,
3005        copy: bool = True,
3006        **opts,
3007    ) -> Select:
3008        """
3009        Append to or set the LATERAL expressions.
3010
3011        Example:
3012            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
3013            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
3014
3015        Args:
3016            *expressions: the SQL code strings to parse.
3017                If an `Expression` instance is passed, it will be used as-is.
3018            append: if `True`, add to any existing expressions.
3019                Otherwise, this resets the expressions.
3020            dialect: the dialect used to parse the input expressions.
3021            copy: if `False`, modify this expression instance in-place.
3022            opts: other options to use to parse the input expressions.
3023
3024        Returns:
3025            The modified Select expression.
3026        """
3027        return _apply_list_builder(
3028            *expressions,
3029            instance=self,
3030            arg="laterals",
3031            append=append,
3032            into=Lateral,
3033            prefix="LATERAL VIEW",
3034            dialect=dialect,
3035            copy=copy,
3036            **opts,
3037        )
3038
3039    def join(
3040        self,
3041        expression: ExpOrStr,
3042        on: t.Optional[ExpOrStr] = None,
3043        using: t.Optional[ExpOrStr | t.Collection[ExpOrStr]] = None,
3044        append: bool = True,
3045        join_type: t.Optional[str] = None,
3046        join_alias: t.Optional[Identifier | str] = None,
3047        dialect: DialectType = None,
3048        copy: bool = True,
3049        **opts,
3050    ) -> Select:
3051        """
3052        Append to or set the JOIN expressions.
3053
3054        Example:
3055            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
3056            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
3057
3058            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
3059            'SELECT 1 FROM a JOIN b USING (x, y, z)'
3060
3061            Use `join_type` to change the type of join:
3062
3063            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
3064            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
3065
3066        Args:
3067            expression: the SQL code string to parse.
3068                If an `Expression` instance is passed, it will be used as-is.
3069            on: optionally specify the join "on" criteria as a SQL string.
3070                If an `Expression` instance is passed, it will be used as-is.
3071            using: optionally specify the join "using" criteria as a SQL string.
3072                If an `Expression` instance is passed, it will be used as-is.
3073            append: if `True`, add to any existing expressions.
3074                Otherwise, this resets the expressions.
3075            join_type: if set, alter the parsed join type.
3076            join_alias: an optional alias for the joined source.
3077            dialect: the dialect used to parse the input expressions.
3078            copy: if `False`, modify this expression instance in-place.
3079            opts: other options to use to parse the input expressions.
3080
3081        Returns:
3082            Select: the modified expression.
3083        """
3084        parse_args: t.Dict[str, t.Any] = {"dialect": dialect, **opts}
3085
3086        try:
3087            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
3088        except ParseError:
3089            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
3090
3091        join = expression if isinstance(expression, Join) else Join(this=expression)
3092
3093        if isinstance(join.this, Select):
3094            join.this.replace(join.this.subquery())
3095
3096        if join_type:
3097            method: t.Optional[Token]
3098            side: t.Optional[Token]
3099            kind: t.Optional[Token]
3100
3101            method, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
3102
3103            if method:
3104                join.set("method", method.text)
3105            if side:
3106                join.set("side", side.text)
3107            if kind:
3108                join.set("kind", kind.text)
3109
3110        if on:
3111            on = and_(*ensure_list(on), dialect=dialect, copy=copy, **opts)
3112            join.set("on", on)
3113
3114        if using:
3115            join = _apply_list_builder(
3116                *ensure_list(using),
3117                instance=join,
3118                arg="using",
3119                append=append,
3120                copy=copy,
3121                into=Identifier,
3122                **opts,
3123            )
3124
3125        if join_alias:
3126            join.set("this", alias_(join.this, join_alias, table=True))
3127
3128        return _apply_list_builder(
3129            join,
3130            instance=self,
3131            arg="joins",
3132            append=append,
3133            copy=copy,
3134            **opts,
3135        )
3136
3137    def where(
3138        self,
3139        *expressions: t.Optional[ExpOrStr],
3140        append: bool = True,
3141        dialect: DialectType = None,
3142        copy: bool = True,
3143        **opts,
3144    ) -> Select:
3145        """
3146        Append to or set the WHERE expressions.
3147
3148        Example:
3149            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
3150            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
3151
3152        Args:
3153            *expressions: the SQL code strings to parse.
3154                If an `Expression` instance is passed, it will be used as-is.
3155                Multiple expressions are combined with an AND operator.
3156            append: if `True`, AND the new expressions to any existing expression.
3157                Otherwise, this resets the expression.
3158            dialect: the dialect used to parse the input expressions.
3159            copy: if `False`, modify this expression instance in-place.
3160            opts: other options to use to parse the input expressions.
3161
3162        Returns:
3163            Select: the modified expression.
3164        """
3165        return _apply_conjunction_builder(
3166            *expressions,
3167            instance=self,
3168            arg="where",
3169            append=append,
3170            into=Where,
3171            dialect=dialect,
3172            copy=copy,
3173            **opts,
3174        )
3175
3176    def having(
3177        self,
3178        *expressions: t.Optional[ExpOrStr],
3179        append: bool = True,
3180        dialect: DialectType = None,
3181        copy: bool = True,
3182        **opts,
3183    ) -> Select:
3184        """
3185        Append to or set the HAVING expressions.
3186
3187        Example:
3188            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
3189            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
3190
3191        Args:
3192            *expressions: the SQL code strings to parse.
3193                If an `Expression` instance is passed, it will be used as-is.
3194                Multiple expressions are combined with an AND operator.
3195            append: if `True`, AND the new expressions to any existing expression.
3196                Otherwise, this resets the expression.
3197            dialect: the dialect used to parse the input expressions.
3198            copy: if `False`, modify this expression instance in-place.
3199            opts: other options to use to parse the input expressions.
3200
3201        Returns:
3202            The modified Select expression.
3203        """
3204        return _apply_conjunction_builder(
3205            *expressions,
3206            instance=self,
3207            arg="having",
3208            append=append,
3209            into=Having,
3210            dialect=dialect,
3211            copy=copy,
3212            **opts,
3213        )
3214
3215    def window(
3216        self,
3217        *expressions: t.Optional[ExpOrStr],
3218        append: bool = True,
3219        dialect: DialectType = None,
3220        copy: bool = True,
3221        **opts,
3222    ) -> Select:
3223        return _apply_list_builder(
3224            *expressions,
3225            instance=self,
3226            arg="windows",
3227            append=append,
3228            into=Window,
3229            dialect=dialect,
3230            copy=copy,
3231            **opts,
3232        )
3233
3234    def qualify(
3235        self,
3236        *expressions: t.Optional[ExpOrStr],
3237        append: bool = True,
3238        dialect: DialectType = None,
3239        copy: bool = True,
3240        **opts,
3241    ) -> Select:
3242        return _apply_conjunction_builder(
3243            *expressions,
3244            instance=self,
3245            arg="qualify",
3246            append=append,
3247            into=Qualify,
3248            dialect=dialect,
3249            copy=copy,
3250            **opts,
3251        )
3252
3253    def distinct(
3254        self, *ons: t.Optional[ExpOrStr], distinct: bool = True, copy: bool = True
3255    ) -> Select:
3256        """
3257        Set the OFFSET expression.
3258
3259        Example:
3260            >>> Select().from_("tbl").select("x").distinct().sql()
3261            'SELECT DISTINCT x FROM tbl'
3262
3263        Args:
3264            ons: the expressions to distinct on
3265            distinct: whether the Select should be distinct
3266            copy: if `False`, modify this expression instance in-place.
3267
3268        Returns:
3269            Select: the modified expression.
3270        """
3271        instance = maybe_copy(self, copy)
3272        on = Tuple(expressions=[maybe_parse(on, copy=copy) for on in ons if on]) if ons else None
3273        instance.set("distinct", Distinct(on=on) if distinct else None)
3274        return instance
3275
3276    def ctas(
3277        self,
3278        table: ExpOrStr,
3279        properties: t.Optional[t.Dict] = None,
3280        dialect: DialectType = None,
3281        copy: bool = True,
3282        **opts,
3283    ) -> Create:
3284        """
3285        Convert this expression to a CREATE TABLE AS statement.
3286
3287        Example:
3288            >>> Select().select("*").from_("tbl").ctas("x").sql()
3289            'CREATE TABLE x AS SELECT * FROM tbl'
3290
3291        Args:
3292            table: the SQL code string to parse as the table name.
3293                If another `Expression` instance is passed, it will be used as-is.
3294            properties: an optional mapping of table properties
3295            dialect: the dialect used to parse the input table.
3296            copy: if `False`, modify this expression instance in-place.
3297            opts: other options to use to parse the input table.
3298
3299        Returns:
3300            The new Create expression.
3301        """
3302        instance = maybe_copy(self, copy)
3303        table_expression = maybe_parse(
3304            table,
3305            into=Table,
3306            dialect=dialect,
3307            **opts,
3308        )
3309        properties_expression = None
3310        if properties:
3311            properties_expression = Properties.from_dict(properties)
3312
3313        return Create(
3314            this=table_expression,
3315            kind="table",
3316            expression=instance,
3317            properties=properties_expression,
3318        )
3319
3320    def lock(self, update: bool = True, copy: bool = True) -> Select:
3321        """
3322        Set the locking read mode for this expression.
3323
3324        Examples:
3325            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
3326            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
3327
3328            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
3329            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
3330
3331        Args:
3332            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
3333            copy: if `False`, modify this expression instance in-place.
3334
3335        Returns:
3336            The modified expression.
3337        """
3338        inst = maybe_copy(self, copy)
3339        inst.set("locks", [Lock(update=update)])
3340
3341        return inst
3342
3343    def hint(self, *hints: ExpOrStr, dialect: DialectType = None, copy: bool = True) -> Select:
3344        """
3345        Set hints for this expression.
3346
3347        Examples:
3348            >>> Select().select("x").from_("tbl").hint("BROADCAST(y)").sql(dialect="spark")
3349            'SELECT /*+ BROADCAST(y) */ x FROM tbl'
3350
3351        Args:
3352            hints: The SQL code strings to parse as the hints.
3353                If an `Expression` instance is passed, it will be used as-is.
3354            dialect: The dialect used to parse the hints.
3355            copy: If `False`, modify this expression instance in-place.
3356
3357        Returns:
3358            The modified expression.
3359        """
3360        inst = maybe_copy(self, copy)
3361        inst.set(
3362            "hint", Hint(expressions=[maybe_parse(h, copy=copy, dialect=dialect) for h in hints])
3363        )
3364
3365        return inst
3366
3367    @property
3368    def named_selects(self) -> t.List[str]:
3369        return [e.output_name for e in self.expressions if e.alias_or_name]
3370
3371    @property
3372    def is_star(self) -> bool:
3373        return any(expression.is_star for expression in self.expressions)
3374
3375    @property
3376    def selects(self) -> t.List[Expression]:
3377        return self.expressions
3378
3379
3380class Subquery(DerivedTable, Unionable):
3381    arg_types = {
3382        "this": True,
3383        "alias": False,
3384        "with": False,
3385        **QUERY_MODIFIERS,
3386    }
3387
3388    def unnest(self):
3389        """
3390        Returns the first non subquery.
3391        """
3392        expression = self
3393        while isinstance(expression, Subquery):
3394            expression = expression.this
3395        return expression
3396
3397    def unwrap(self) -> Subquery:
3398        expression = self
3399        while expression.same_parent and expression.is_wrapper:
3400            expression = t.cast(Subquery, expression.parent)
3401        return expression
3402
3403    @property
3404    def is_wrapper(self) -> bool:
3405        """
3406        Whether this Subquery acts as a simple wrapper around another expression.
3407
3408        SELECT * FROM (((SELECT * FROM t)))
3409                      ^
3410                      This corresponds to a "wrapper" Subquery node
3411        """
3412        return all(v is None for k, v in self.args.items() if k != "this")
3413
3414    @property
3415    def is_star(self) -> bool:
3416        return self.this.is_star
3417
3418    @property
3419    def output_name(self) -> str:
3420        return self.alias
3421
3422
3423class TableSample(Expression):
3424    arg_types = {
3425        "this": False,
3426        "expressions": False,
3427        "method": False,
3428        "bucket_numerator": False,
3429        "bucket_denominator": False,
3430        "bucket_field": False,
3431        "percent": False,
3432        "rows": False,
3433        "size": False,
3434        "seed": False,
3435        "kind": False,
3436    }
3437
3438
3439class Tag(Expression):
3440    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
3441
3442    arg_types = {
3443        "this": False,
3444        "prefix": False,
3445        "postfix": False,
3446    }
3447
3448
3449# Represents both the standard SQL PIVOT operator and DuckDB's "simplified" PIVOT syntax
3450# https://duckdb.org/docs/sql/statements/pivot
3451class Pivot(Expression):
3452    arg_types = {
3453        "this": False,
3454        "alias": False,
3455        "expressions": False,
3456        "field": False,
3457        "unpivot": False,
3458        "using": False,
3459        "group": False,
3460        "columns": False,
3461        "include_nulls": False,
3462    }
3463
3464
3465class Window(Condition):
3466    arg_types = {
3467        "this": True,
3468        "partition_by": False,
3469        "order": False,
3470        "spec": False,
3471        "alias": False,
3472        "over": False,
3473        "first": False,
3474    }
3475
3476
3477class WindowSpec(Expression):
3478    arg_types = {
3479        "kind": False,
3480        "start": False,
3481        "start_side": False,
3482        "end": False,
3483        "end_side": False,
3484    }
3485
3486
3487class Where(Expression):
3488    pass
3489
3490
3491class Star(Expression):
3492    arg_types = {"except": False, "replace": False}
3493
3494    @property
3495    def name(self) -> str:
3496        return "*"
3497
3498    @property
3499    def output_name(self) -> str:
3500        return self.name
3501
3502
3503class Parameter(Condition):
3504    arg_types = {"this": True, "wrapped": False}
3505
3506
3507class SessionParameter(Condition):
3508    arg_types = {"this": True, "kind": False}
3509
3510
3511class Placeholder(Condition):
3512    arg_types = {"this": False, "kind": False}
3513
3514
3515class Null(Condition):
3516    arg_types: t.Dict[str, t.Any] = {}
3517
3518    @property
3519    def name(self) -> str:
3520        return "NULL"
3521
3522
3523class Boolean(Condition):
3524    pass
3525
3526
3527class DataTypeParam(Expression):
3528    arg_types = {"this": True, "expression": False}
3529
3530
3531class DataType(Expression):
3532    arg_types = {
3533        "this": True,
3534        "expressions": False,
3535        "nested": False,
3536        "values": False,
3537        "prefix": False,
3538        "kind": False,
3539    }
3540
3541    class Type(AutoName):
3542        ARRAY = auto()
3543        BIGDECIMAL = auto()
3544        BIGINT = auto()
3545        BIGSERIAL = auto()
3546        BINARY = auto()
3547        BIT = auto()
3548        BOOLEAN = auto()
3549        CHAR = auto()
3550        DATE = auto()
3551        DATEMULTIRANGE = auto()
3552        DATERANGE = auto()
3553        DATETIME = auto()
3554        DATETIME64 = auto()
3555        DECIMAL = auto()
3556        DOUBLE = auto()
3557        ENUM = auto()
3558        ENUM8 = auto()
3559        ENUM16 = auto()
3560        FIXEDSTRING = auto()
3561        FLOAT = auto()
3562        GEOGRAPHY = auto()
3563        GEOMETRY = auto()
3564        HLLSKETCH = auto()
3565        HSTORE = auto()
3566        IMAGE = auto()
3567        INET = auto()
3568        INT = auto()
3569        INT128 = auto()
3570        INT256 = auto()
3571        INT4MULTIRANGE = auto()
3572        INT4RANGE = auto()
3573        INT8MULTIRANGE = auto()
3574        INT8RANGE = auto()
3575        INTERVAL = auto()
3576        IPADDRESS = auto()
3577        IPPREFIX = auto()
3578        JSON = auto()
3579        JSONB = auto()
3580        LONGBLOB = auto()
3581        LONGTEXT = auto()
3582        LOWCARDINALITY = auto()
3583        MAP = auto()
3584        MEDIUMBLOB = auto()
3585        MEDIUMINT = auto()
3586        MEDIUMTEXT = auto()
3587        MONEY = auto()
3588        NCHAR = auto()
3589        NESTED = auto()
3590        NULL = auto()
3591        NULLABLE = auto()
3592        NUMMULTIRANGE = auto()
3593        NUMRANGE = auto()
3594        NVARCHAR = auto()
3595        OBJECT = auto()
3596        ROWVERSION = auto()
3597        SERIAL = auto()
3598        SET = auto()
3599        SMALLINT = auto()
3600        SMALLMONEY = auto()
3601        SMALLSERIAL = auto()
3602        STRUCT = auto()
3603        SUPER = auto()
3604        TEXT = auto()
3605        TINYBLOB = auto()
3606        TINYTEXT = auto()
3607        TIME = auto()
3608        TIMETZ = auto()
3609        TIMESTAMP = auto()
3610        TIMESTAMPLTZ = auto()
3611        TIMESTAMPTZ = auto()
3612        TIMESTAMP_S = auto()
3613        TIMESTAMP_MS = auto()
3614        TIMESTAMP_NS = auto()
3615        TINYINT = auto()
3616        TSMULTIRANGE = auto()
3617        TSRANGE = auto()
3618        TSTZMULTIRANGE = auto()
3619        TSTZRANGE = auto()
3620        UBIGINT = auto()
3621        UINT = auto()
3622        UINT128 = auto()
3623        UINT256 = auto()
3624        UMEDIUMINT = auto()
3625        UDECIMAL = auto()
3626        UNIQUEIDENTIFIER = auto()
3627        UNKNOWN = auto()  # Sentinel value, useful for type annotation
3628        USERDEFINED = "USER-DEFINED"
3629        USMALLINT = auto()
3630        UTINYINT = auto()
3631        UUID = auto()
3632        VARBINARY = auto()
3633        VARCHAR = auto()
3634        VARIANT = auto()
3635        XML = auto()
3636        YEAR = auto()
3637
3638    TEXT_TYPES = {
3639        Type.CHAR,
3640        Type.NCHAR,
3641        Type.VARCHAR,
3642        Type.NVARCHAR,
3643        Type.TEXT,
3644    }
3645
3646    INTEGER_TYPES = {
3647        Type.INT,
3648        Type.TINYINT,
3649        Type.SMALLINT,
3650        Type.BIGINT,
3651        Type.INT128,
3652        Type.INT256,
3653    }
3654
3655    FLOAT_TYPES = {
3656        Type.FLOAT,
3657        Type.DOUBLE,
3658    }
3659
3660    NUMERIC_TYPES = {
3661        *INTEGER_TYPES,
3662        *FLOAT_TYPES,
3663    }
3664
3665    TEMPORAL_TYPES = {
3666        Type.TIME,
3667        Type.TIMETZ,
3668        Type.TIMESTAMP,
3669        Type.TIMESTAMPTZ,
3670        Type.TIMESTAMPLTZ,
3671        Type.TIMESTAMP_S,
3672        Type.TIMESTAMP_MS,
3673        Type.TIMESTAMP_NS,
3674        Type.DATE,
3675        Type.DATETIME,
3676        Type.DATETIME64,
3677    }
3678
3679    @classmethod
3680    def build(
3681        cls,
3682        dtype: str | DataType | DataType.Type,
3683        dialect: DialectType = None,
3684        udt: bool = False,
3685        **kwargs,
3686    ) -> DataType:
3687        """
3688        Constructs a DataType object.
3689
3690        Args:
3691            dtype: the data type of interest.
3692            dialect: the dialect to use for parsing `dtype`, in case it's a string.
3693            udt: when set to True, `dtype` will be used as-is if it can't be parsed into a
3694                DataType, thus creating a user-defined type.
3695            kawrgs: additional arguments to pass in the constructor of DataType.
3696
3697        Returns:
3698            The constructed DataType object.
3699        """
3700        from sqlglot import parse_one
3701
3702        if isinstance(dtype, str):
3703            if dtype.upper() == "UNKNOWN":
3704                return DataType(this=DataType.Type.UNKNOWN, **kwargs)
3705
3706            try:
3707                data_type_exp = parse_one(
3708                    dtype, read=dialect, into=DataType, error_level=ErrorLevel.IGNORE
3709                )
3710            except ParseError:
3711                if udt:
3712                    return DataType(this=DataType.Type.USERDEFINED, kind=dtype, **kwargs)
3713                raise
3714        elif isinstance(dtype, DataType.Type):
3715            data_type_exp = DataType(this=dtype)
3716        elif isinstance(dtype, DataType):
3717            return dtype
3718        else:
3719            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
3720
3721        return DataType(**{**data_type_exp.args, **kwargs})
3722
3723    def is_type(self, *dtypes: str | DataType | DataType.Type) -> bool:
3724        """
3725        Checks whether this DataType matches one of the provided data types. Nested types or precision
3726        will be compared using "structural equivalence" semantics, so e.g. array<int> != array<float>.
3727
3728        Args:
3729            dtypes: the data types to compare this DataType to.
3730
3731        Returns:
3732            True, if and only if there is a type in `dtypes` which is equal to this DataType.
3733        """
3734        for dtype in dtypes:
3735            other = DataType.build(dtype, udt=True)
3736
3737            if (
3738                other.expressions
3739                or self.this == DataType.Type.USERDEFINED
3740                or other.this == DataType.Type.USERDEFINED
3741            ):
3742                matches = self == other
3743            else:
3744                matches = self.this == other.this
3745
3746            if matches:
3747                return True
3748        return False
3749
3750
3751# https://www.postgresql.org/docs/15/datatype-pseudo.html
3752class PseudoType(DataType):
3753    arg_types = {"this": True}
3754
3755
3756# https://www.postgresql.org/docs/15/datatype-oid.html
3757class ObjectIdentifier(DataType):
3758    arg_types = {"this": True}
3759
3760
3761# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
3762class SubqueryPredicate(Predicate):
3763    pass
3764
3765
3766class All(SubqueryPredicate):
3767    pass
3768
3769
3770class Any(SubqueryPredicate):
3771    pass
3772
3773
3774class Exists(SubqueryPredicate):
3775    pass
3776
3777
3778# Commands to interact with the databases or engines. For most of the command
3779# expressions we parse whatever comes after the command's name as a string.
3780class Command(Expression):
3781    arg_types = {"this": True, "expression": False}
3782
3783
3784class Transaction(Expression):
3785    arg_types = {"this": False, "modes": False, "mark": False}
3786
3787
3788class Commit(Expression):
3789    arg_types = {"chain": False, "this": False, "durability": False}
3790
3791
3792class Rollback(Expression):
3793    arg_types = {"savepoint": False, "this": False}
3794
3795
3796class AlterTable(Expression):
3797    arg_types = {"this": True, "actions": True, "exists": False, "only": False}
3798
3799
3800class AddConstraint(Expression):
3801    arg_types = {"this": False, "expression": False, "enforced": False}
3802
3803
3804class DropPartition(Expression):
3805    arg_types = {"expressions": True, "exists": False}
3806
3807
3808# Binary expressions like (ADD a b)
3809class Binary(Condition):
3810    arg_types = {"this": True, "expression": True}
3811
3812    @property
3813    def left(self) -> Expression:
3814        return self.this
3815
3816    @property
3817    def right(self) -> Expression:
3818        return self.expression
3819
3820
3821class Add(Binary):
3822    pass
3823
3824
3825class Connector(Binary):
3826    pass
3827
3828
3829class And(Connector):
3830    pass
3831
3832
3833class Or(Connector):
3834    pass
3835
3836
3837class BitwiseAnd(Binary):
3838    pass
3839
3840
3841class BitwiseLeftShift(Binary):
3842    pass
3843
3844
3845class BitwiseOr(Binary):
3846    pass
3847
3848
3849class BitwiseRightShift(Binary):
3850    pass
3851
3852
3853class BitwiseXor(Binary):
3854    pass
3855
3856
3857class Div(Binary):
3858    pass
3859
3860
3861class Overlaps(Binary):
3862    pass
3863
3864
3865class Dot(Binary):
3866    @property
3867    def name(self) -> str:
3868        return self.expression.name
3869
3870    @property
3871    def output_name(self) -> str:
3872        return self.name
3873
3874    @classmethod
3875    def build(self, expressions: t.Sequence[Expression]) -> Dot:
3876        """Build a Dot object with a sequence of expressions."""
3877        if len(expressions) < 2:
3878            raise ValueError(f"Dot requires >= 2 expressions.")
3879
3880        return t.cast(Dot, reduce(lambda x, y: Dot(this=x, expression=y), expressions))
3881
3882
3883class DPipe(Binary):
3884    pass
3885
3886
3887class SafeDPipe(DPipe):
3888    pass
3889
3890
3891class EQ(Binary, Predicate):
3892    pass
3893
3894
3895class NullSafeEQ(Binary, Predicate):
3896    pass
3897
3898
3899class NullSafeNEQ(Binary, Predicate):
3900    pass
3901
3902
3903class Distance(Binary):
3904    pass
3905
3906
3907class Escape(Binary):
3908    pass
3909
3910
3911class Glob(Binary, Predicate):
3912    pass
3913
3914
3915class GT(Binary, Predicate):
3916    pass
3917
3918
3919class GTE(Binary, Predicate):
3920    pass
3921
3922
3923class ILike(Binary, Predicate):
3924    pass
3925
3926
3927class ILikeAny(Binary, Predicate):
3928    pass
3929
3930
3931class IntDiv(Binary):
3932    pass
3933
3934
3935class Is(Binary, Predicate):
3936    pass
3937
3938
3939class Kwarg(Binary):
3940    """Kwarg in special functions like func(kwarg => y)."""
3941
3942
3943class Like(Binary, Predicate):
3944    pass
3945
3946
3947class LikeAny(Binary, Predicate):
3948    pass
3949
3950
3951class LT(Binary, Predicate):
3952    pass
3953
3954
3955class LTE(Binary, Predicate):
3956    pass
3957
3958
3959class Mod(Binary):
3960    pass
3961
3962
3963class Mul(Binary):
3964    pass
3965
3966
3967class NEQ(Binary, Predicate):
3968    pass
3969
3970
3971class SimilarTo(Binary, Predicate):
3972    pass
3973
3974
3975class Slice(Binary):
3976    arg_types = {"this": False, "expression": False}
3977
3978
3979class Sub(Binary):
3980    pass
3981
3982
3983class ArrayOverlaps(Binary):
3984    pass
3985
3986
3987# Unary Expressions
3988# (NOT a)
3989class Unary(Condition):
3990    pass
3991
3992
3993class BitwiseNot(Unary):
3994    pass
3995
3996
3997class Not(Unary):
3998    pass
3999
4000
4001class Paren(Unary):
4002    arg_types = {"this": True, "with": False}
4003
4004    @property
4005    def output_name(self) -> str:
4006        return self.this.name
4007
4008
4009class Neg(Unary):
4010    pass
4011
4012
4013class Alias(Expression):
4014    arg_types = {"this": True, "alias": False}
4015
4016    @property
4017    def output_name(self) -> str:
4018        return self.alias
4019
4020
4021class Aliases(Expression):
4022    arg_types = {"this": True, "expressions": True}
4023
4024    @property
4025    def aliases(self):
4026        return self.expressions
4027
4028
4029class AtTimeZone(Expression):
4030    arg_types = {"this": True, "zone": True}
4031
4032
4033class Between(Predicate):
4034    arg_types = {"this": True, "low": True, "high": True}
4035
4036
4037class Bracket(Condition):
4038    arg_types = {"this": True, "expressions": True}
4039
4040    @property
4041    def output_name(self) -> str:
4042        if len(self.expressions) == 1:
4043            return self.expressions[0].output_name
4044
4045        return super().output_name
4046
4047
4048class SafeBracket(Bracket):
4049    """Represents array lookup where OOB index yields NULL instead of causing a failure."""
4050
4051
4052class Distinct(Expression):
4053    arg_types = {"expressions": False, "on": False}
4054
4055
4056class In(Predicate):
4057    arg_types = {
4058        "this": True,
4059        "expressions": False,
4060        "query": False,
4061        "unnest": False,
4062        "field": False,
4063        "is_global": False,
4064    }
4065
4066
4067class TimeUnit(Expression):
4068    """Automatically converts unit arg into a var."""
4069
4070    arg_types = {"unit": False}
4071
4072    UNABBREVIATED_UNIT_NAME = {
4073        "d": "day",
4074        "h": "hour",
4075        "m": "minute",
4076        "ms": "millisecond",
4077        "ns": "nanosecond",
4078        "q": "quarter",
4079        "s": "second",
4080        "us": "microsecond",
4081        "w": "week",
4082        "y": "year",
4083    }
4084
4085    VAR_LIKE = (Column, Literal, Var)
4086
4087    def __init__(self, **args):
4088        unit = args.get("unit")
4089        if isinstance(unit, self.VAR_LIKE):
4090            args["unit"] = Var(this=self.UNABBREVIATED_UNIT_NAME.get(unit.name) or unit.name)
4091        elif isinstance(unit, Week):
4092            unit.set("this", Var(this=unit.this.name))
4093
4094        super().__init__(**args)
4095
4096    @property
4097    def unit(self) -> t.Optional[Var]:
4098        return self.args.get("unit")
4099
4100
4101class IntervalOp(TimeUnit):
4102    arg_types = {"unit": True, "expression": True}
4103
4104    def interval(self):
4105        return Interval(
4106            this=self.expression.copy(),
4107            unit=self.unit.copy(),
4108        )
4109
4110
4111# https://www.oracletutorial.com/oracle-basics/oracle-interval/
4112# https://trino.io/docs/current/language/types.html#interval-day-to-second
4113# https://docs.databricks.com/en/sql/language-manual/data-types/interval-type.html
4114class IntervalSpan(DataType):
4115    arg_types = {"this": True, "expression": True}
4116
4117
4118class Interval(TimeUnit):
4119    arg_types = {"this": False, "unit": False}
4120
4121
4122class IgnoreNulls(Expression):
4123    pass
4124
4125
4126class RespectNulls(Expression):
4127    pass
4128
4129
4130# Functions
4131class Func(Condition):
4132    """
4133    The base class for all function expressions.
4134
4135    Attributes:
4136        is_var_len_args (bool): if set to True the last argument defined in arg_types will be
4137            treated as a variable length argument and the argument's value will be stored as a list.
4138        _sql_names (list): determines the SQL name (1st item in the list) and aliases (subsequent items)
4139            for this function expression. These values are used to map this node to a name during parsing
4140            as well as to provide the function's name during SQL string generation. By default the SQL
4141            name is set to the expression's class name transformed to snake case.
4142    """
4143
4144    is_var_len_args = False
4145
4146    @classmethod
4147    def from_arg_list(cls, args):
4148        if cls.is_var_len_args:
4149            all_arg_keys = list(cls.arg_types)
4150            # If this function supports variable length argument treat the last argument as such.
4151            non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys
4152            num_non_var = len(non_var_len_arg_keys)
4153
4154            args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)}
4155            args_dict[all_arg_keys[-1]] = args[num_non_var:]
4156        else:
4157            args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)}
4158
4159        return cls(**args_dict)
4160
4161    @classmethod
4162    def sql_names(cls):
4163        if cls is Func:
4164            raise NotImplementedError(
4165                "SQL name is only supported by concrete function implementations"
4166            )
4167        if "_sql_names" not in cls.__dict__:
4168            cls._sql_names = [camel_to_snake_case(cls.__name__)]
4169        return cls._sql_names
4170
4171    @classmethod
4172    def sql_name(cls):
4173        return cls.sql_names()[0]
4174
4175    @classmethod
4176    def default_parser_mappings(cls):
4177        return {name: cls.from_arg_list for name in cls.sql_names()}
4178
4179
4180class AggFunc(Func):
4181    pass
4182
4183
4184class ParameterizedAgg(AggFunc):
4185    arg_types = {"this": True, "expressions": True, "params": True}
4186
4187
4188class Abs(Func):
4189    pass
4190
4191
4192class ArgMax(AggFunc):
4193    arg_types = {"this": True, "expression": True, "count": False}
4194    _sql_names = ["ARG_MAX", "ARGMAX", "MAX_BY"]
4195
4196
4197class ArgMin(AggFunc):
4198    arg_types = {"this": True, "expression": True, "count": False}
4199    _sql_names = ["ARG_MIN", "ARGMIN", "MIN_BY"]
4200
4201
4202class ApproxTopK(AggFunc):
4203    arg_types = {"this": True, "expression": False, "counters": False}
4204
4205
4206class Flatten(Func):
4207    pass
4208
4209
4210# https://spark.apache.org/docs/latest/api/sql/index.html#transform
4211class Transform(Func):
4212    arg_types = {"this": True, "expression": True}
4213
4214
4215class Anonymous(Func):
4216    arg_types = {"this": True, "expressions": False}
4217    is_var_len_args = True
4218
4219
4220# https://docs.snowflake.com/en/sql-reference/functions/hll
4221# https://docs.aws.amazon.com/redshift/latest/dg/r_HLL_function.html
4222class Hll(AggFunc):
4223    arg_types = {"this": True, "expressions": False}
4224    is_var_len_args = True
4225
4226
4227class ApproxDistinct(AggFunc):
4228    arg_types = {"this": True, "accuracy": False}
4229    _sql_names = ["APPROX_DISTINCT", "APPROX_COUNT_DISTINCT"]
4230
4231
4232class Array(Func):
4233    arg_types = {"expressions": False}
4234    is_var_len_args = True
4235
4236
4237# https://docs.snowflake.com/en/sql-reference/functions/to_char
4238class ToChar(Func):
4239    arg_types = {"this": True, "format": False}
4240
4241
4242class GenerateSeries(Func):
4243    arg_types = {"start": True, "end": True, "step": False}
4244
4245
4246class ArrayAgg(AggFunc):
4247    pass
4248
4249
4250class ArrayAll(Func):
4251    arg_types = {"this": True, "expression": True}
4252
4253
4254class ArrayAny(Func):
4255    arg_types = {"this": True, "expression": True}
4256
4257
4258class ArrayConcat(Func):
4259    _sql_names = ["ARRAY_CONCAT", "ARRAY_CAT"]
4260    arg_types = {"this": True, "expressions": False}
4261    is_var_len_args = True
4262
4263
4264class ArrayContains(Binary, Func):
4265    pass
4266
4267
4268class ArrayContained(Binary):
4269    pass
4270
4271
4272class ArrayFilter(Func):
4273    arg_types = {"this": True, "expression": True}
4274    _sql_names = ["FILTER", "ARRAY_FILTER"]
4275
4276
4277class ArrayJoin(Func):
4278    arg_types = {"this": True, "expression": True, "null": False}
4279
4280
4281class ArraySize(Func):
4282    arg_types = {"this": True, "expression": False}
4283
4284
4285class ArraySort(Func):
4286    arg_types = {"this": True, "expression": False}
4287
4288
4289class ArraySum(Func):
4290    pass
4291
4292
4293class ArrayUnionAgg(AggFunc):
4294    pass
4295
4296
4297class Avg(AggFunc):
4298    pass
4299
4300
4301class AnyValue(AggFunc):
4302    arg_types = {"this": True, "having": False, "max": False, "ignore_nulls": False}
4303
4304
4305class First(Func):
4306    arg_types = {"this": True, "ignore_nulls": False}
4307
4308
4309class Last(Func):
4310    arg_types = {"this": True, "ignore_nulls": False}
4311
4312
4313class Case(Func):
4314    arg_types = {"this": False, "ifs": True, "default": False}
4315
4316    def when(self, condition: ExpOrStr, then: ExpOrStr, copy: bool = True, **opts) -> Case:
4317        instance = maybe_copy(self, copy)
4318        instance.append(
4319            "ifs",
4320            If(
4321                this=maybe_parse(condition, copy=copy, **opts),
4322                true=maybe_parse(then, copy=copy, **opts),
4323            ),
4324        )
4325        return instance
4326
4327    def else_(self, condition: ExpOrStr, copy: bool = True, **opts) -> Case:
4328        instance = maybe_copy(self, copy)
4329        instance.set("default", maybe_parse(condition, copy=copy, **opts))
4330        return instance
4331
4332
4333class Cast(Func):
4334    arg_types = {"this": True, "to": True, "format": False, "safe": False}
4335
4336    @property
4337    def name(self) -> str:
4338        return self.this.name
4339
4340    @property
4341    def to(self) -> DataType:
4342        return self.args["to"]
4343
4344    @property
4345    def output_name(self) -> str:
4346        return self.name
4347
4348    def is_type(self, *dtypes: str | DataType | DataType.Type) -> bool:
4349        """
4350        Checks whether this Cast's DataType matches one of the provided data types. Nested types
4351        like arrays or structs will be compared using "structural equivalence" semantics, so e.g.
4352        array<int> != array<float>.
4353
4354        Args:
4355            dtypes: the data types to compare this Cast's DataType to.
4356
4357        Returns:
4358            True, if and only if there is a type in `dtypes` which is equal to this Cast's DataType.
4359        """
4360        return self.to.is_type(*dtypes)
4361
4362
4363class TryCast(Cast):
4364    pass
4365
4366
4367class CastToStrType(Func):
4368    arg_types = {"this": True, "to": True}
4369
4370
4371class Collate(Binary, Func):
4372    pass
4373
4374
4375class Ceil(Func):
4376    arg_types = {"this": True, "decimals": False}
4377    _sql_names = ["CEIL", "CEILING"]
4378
4379
4380class Coalesce(Func):
4381    arg_types = {"this": True, "expressions": False}
4382    is_var_len_args = True
4383    _sql_names = ["COALESCE", "IFNULL", "NVL"]
4384
4385
4386class Chr(Func):
4387    arg_types = {"this": True, "charset": False, "expressions": False}
4388    is_var_len_args = True
4389    _sql_names = ["CHR", "CHAR"]
4390
4391
4392class Concat(Func):
4393    arg_types = {"expressions": True}
4394    is_var_len_args = True
4395
4396
4397class SafeConcat(Concat):
4398    pass
4399
4400
4401class ConcatWs(Concat):
4402    _sql_names = ["CONCAT_WS"]
4403
4404
4405class Count(AggFunc):
4406    arg_types = {"this": False, "expressions": False}
4407    is_var_len_args = True
4408
4409
4410class CountIf(AggFunc):
4411    pass
4412
4413
4414class CurrentDate(Func):
4415    arg_types = {"this": False}
4416
4417
4418class CurrentDatetime(Func):
4419    arg_types = {"this": False}
4420
4421
4422class CurrentTime(Func):
4423    arg_types = {"this": False}
4424
4425
4426class CurrentTimestamp(Func):
4427    arg_types = {"this": False}
4428
4429
4430class CurrentUser(Func):
4431    arg_types = {"this": False}
4432
4433
4434class DateAdd(Func, IntervalOp):
4435    arg_types = {"this": True, "expression": True, "unit": False}
4436
4437
4438class DateSub(Func, IntervalOp):
4439    arg_types = {"this": True, "expression": True, "unit": False}
4440
4441
4442class DateDiff(Func, TimeUnit):
4443    _sql_names = ["DATEDIFF", "DATE_DIFF"]
4444    arg_types = {"this": True, "expression": True, "unit": False}
4445
4446
4447class DateTrunc(Func):
4448    arg_types = {"unit": True, "this": True, "zone": False}
4449
4450    @property
4451    def unit(self) -> Expression:
4452        return self.args["unit"]
4453
4454
4455class DatetimeAdd(Func, IntervalOp):
4456    arg_types = {"this": True, "expression": True, "unit": False}
4457
4458
4459class DatetimeSub(Func, IntervalOp):
4460    arg_types = {"this": True, "expression": True, "unit": False}
4461
4462
4463class DatetimeDiff(Func, TimeUnit):
4464    arg_types = {"this": True, "expression": True, "unit": False}
4465
4466
4467class DatetimeTrunc(Func, TimeUnit):
4468    arg_types = {"this": True, "unit": True, "zone": False}
4469
4470
4471class DayOfWeek(Func):
4472    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
4473
4474
4475class DayOfMonth(Func):
4476    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
4477
4478
4479class DayOfYear(Func):
4480    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
4481
4482
4483class ToDays(Func):
4484    pass
4485
4486
4487class WeekOfYear(Func):
4488    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
4489
4490
4491class MonthsBetween(Func):
4492    arg_types = {"this": True, "expression": True, "roundoff": False}
4493
4494
4495class LastDateOfMonth(Func):
4496    pass
4497
4498
4499class Extract(Func):
4500    arg_types = {"this": True, "expression": True}
4501
4502
4503class Timestamp(Func):
4504    arg_types = {"this": False, "expression": False}
4505
4506
4507class TimestampAdd(Func, TimeUnit):
4508    arg_types = {"this": True, "expression": True, "unit": False}
4509
4510
4511class TimestampSub(Func, TimeUnit):
4512    arg_types = {"this": True, "expression": True, "unit": False}
4513
4514
4515class TimestampDiff(Func, TimeUnit):
4516    arg_types = {"this": True, "expression": True, "unit": False}
4517
4518
4519class TimestampTrunc(Func, TimeUnit):
4520    arg_types = {"this": True, "unit": True, "zone": False}
4521
4522
4523class TimeAdd(Func, TimeUnit):
4524    arg_types = {"this": True, "expression": True, "unit": False}
4525
4526
4527class TimeSub(Func, TimeUnit):
4528    arg_types = {"this": True, "expression": True, "unit": False}
4529
4530
4531class TimeDiff(Func, TimeUnit):
4532    arg_types = {"this": True, "expression": True, "unit": False}
4533
4534
4535class TimeTrunc(Func, TimeUnit):
4536    arg_types = {"this": True, "unit": True, "zone": False}
4537
4538
4539class DateFromParts(Func):
4540    _sql_names = ["DATEFROMPARTS"]
4541    arg_types = {"year": True, "month": True, "day": True}
4542
4543
4544class DateStrToDate(Func):
4545    pass
4546
4547
4548class DateToDateStr(Func):
4549    pass
4550
4551
4552class DateToDi(Func):
4553    pass
4554
4555
4556# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date
4557class Date(Func):
4558    arg_types = {"this": False, "zone": False, "expressions": False}
4559    is_var_len_args = True
4560
4561
4562class Day(Func):
4563    pass
4564
4565
4566class Decode(Func):
4567    arg_types = {"this": True, "charset": True, "replace": False}
4568
4569
4570class DiToDate(Func):
4571    pass
4572
4573
4574class Encode(Func):
4575    arg_types = {"this": True, "charset": True}
4576
4577
4578class Exp(Func):
4579    pass
4580
4581
4582# https://docs.snowflake.com/en/sql-reference/functions/flatten
4583class Explode(Func):
4584    arg_types = {"this": True, "expressions": False}
4585    is_var_len_args = True
4586
4587
4588class ExplodeOuter(Explode):
4589    pass
4590
4591
4592class Posexplode(Explode):
4593    pass
4594
4595
4596class PosexplodeOuter(Posexplode):
4597    pass
4598
4599
4600class Floor(Func):
4601    arg_types = {"this": True, "decimals": False}
4602
4603
4604class FromBase64(Func):
4605    pass
4606
4607
4608class ToBase64(Func):
4609    pass
4610
4611
4612class Greatest(Func):
4613    arg_types = {"this": True, "expressions": False}
4614    is_var_len_args = True
4615
4616
4617class GroupConcat(AggFunc):
4618    arg_types = {"this": True, "separator": False}
4619
4620
4621class Hex(Func):
4622    pass
4623
4624
4625class Xor(Connector, Func):
4626    arg_types = {"this": False, "expression": False, "expressions": False}
4627
4628
4629class If(Func):
4630    arg_types = {"this": True, "true": True, "false": False}
4631
4632
4633class Initcap(Func):
4634    arg_types = {"this": True, "expression": False}
4635
4636
4637class IsNan(Func):
4638    _sql_names = ["IS_NAN", "ISNAN"]
4639
4640
4641class FormatJson(Expression):
4642    pass
4643
4644
4645class JSONKeyValue(Expression):
4646    arg_types = {"this": True, "expression": True}
4647
4648
4649class JSONObject(Func):
4650    arg_types = {
4651        "expressions": False,
4652        "null_handling": False,
4653        "unique_keys": False,
4654        "return_type": False,
4655        "encoding": False,
4656    }
4657
4658
4659# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAY.html
4660class JSONArray(Func):
4661    arg_types = {
4662        "expressions": True,
4663        "null_handling": False,
4664        "return_type": False,
4665        "strict": False,
4666    }
4667
4668
4669# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAYAGG.html
4670class JSONArrayAgg(Func):
4671    arg_types = {
4672        "this": True,
4673        "order": False,
4674        "null_handling": False,
4675        "return_type": False,
4676        "strict": False,
4677    }
4678
4679
4680# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
4681# Note: parsing of JSON column definitions is currently incomplete.
4682class JSONColumnDef(Expression):
4683    arg_types = {"this": False, "kind": False, "path": False, "nested_schema": False}
4684
4685
4686class JSONSchema(Expression):
4687    arg_types = {"expressions": True}
4688
4689
4690# # https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
4691class JSONTable(Func):
4692    arg_types = {
4693        "this": True,
4694        "schema": True,
4695        "path": False,
4696        "error_handling": False,
4697        "empty_handling": False,
4698    }
4699
4700
4701class OpenJSONColumnDef(Expression):
4702    arg_types = {"this": True, "kind": True, "path": False, "as_json": False}
4703
4704
4705class OpenJSON(Func):
4706    arg_types = {"this": True, "path": False, "expressions": False}
4707
4708
4709class JSONBContains(Binary):
4710    _sql_names = ["JSONB_CONTAINS"]
4711
4712
4713class JSONExtract(Binary, Func):
4714    _sql_names = ["JSON_EXTRACT"]
4715
4716
4717class JSONExtractScalar(JSONExtract):
4718    _sql_names = ["JSON_EXTRACT_SCALAR"]
4719
4720
4721class JSONBExtract(JSONExtract):
4722    _sql_names = ["JSONB_EXTRACT"]
4723
4724
4725class JSONBExtractScalar(JSONExtract):
4726    _sql_names = ["JSONB_EXTRACT_SCALAR"]
4727
4728
4729class JSONFormat(Func):
4730    arg_types = {"this": False, "options": False}
4731    _sql_names = ["JSON_FORMAT"]
4732
4733
4734# https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_member-of
4735class JSONArrayContains(Binary, Predicate, Func):
4736    _sql_names = ["JSON_ARRAY_CONTAINS"]
4737
4738
4739class ParseJSON(Func):
4740    # BigQuery, Snowflake have PARSE_JSON, Presto has JSON_PARSE
4741    _sql_names = ["PARSE_JSON", "JSON_PARSE"]
4742    arg_types = {"this": True, "expressions": False}
4743    is_var_len_args = True
4744
4745
4746class Least(Func):
4747    arg_types = {"this": True, "expressions": False}
4748    is_var_len_args = True
4749
4750
4751class Left(Func):
4752    arg_types = {"this": True, "expression": True}
4753
4754
4755class Right(Func):
4756    arg_types = {"this": True, "expression": True}
4757
4758
4759class Length(Func):
4760    _sql_names = ["LENGTH", "LEN"]
4761
4762
4763class Levenshtein(Func):
4764    arg_types = {
4765        "this": True,
4766        "expression": False,
4767        "ins_cost": False,
4768        "del_cost": False,
4769        "sub_cost": False,
4770    }
4771
4772
4773class Ln(Func):
4774    pass
4775
4776
4777class Log(Func):
4778    arg_types = {"this": True, "expression": False}
4779
4780
4781class Log2(Func):
4782    pass
4783
4784
4785class Log10(Func):
4786    pass
4787
4788
4789class LogicalOr(AggFunc):
4790    _sql_names = ["LOGICAL_OR", "BOOL_OR", "BOOLOR_AGG"]
4791
4792
4793class LogicalAnd(AggFunc):
4794    _sql_names = ["LOGICAL_AND", "BOOL_AND", "BOOLAND_AGG"]
4795
4796
4797class Lower(Func):
4798    _sql_names = ["LOWER", "LCASE"]
4799
4800
4801class Map(Func):
4802    arg_types = {"keys": False, "values": False}
4803
4804    @property
4805    def keys(self) -> t.List[Expression]:
4806        keys = self.args.get("keys")
4807        return keys.expressions if keys else []
4808
4809    @property
4810    def values(self) -> t.List[Expression]:
4811        values = self.args.get("values")
4812        return values.expressions if values else []
4813
4814
4815class MapFromEntries(Func):
4816    pass
4817
4818
4819class StarMap(Func):
4820    pass
4821
4822
4823class VarMap(Func):
4824    arg_types = {"keys": True, "values": True}
4825    is_var_len_args = True
4826
4827    @property
4828    def keys(self) -> t.List[Expression]:
4829        return self.args["keys"].expressions
4830
4831    @property
4832    def values(self) -> t.List[Expression]:
4833        return self.args["values"].expressions
4834
4835
4836# https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
4837class MatchAgainst(Func):
4838    arg_types = {"this": True, "expressions": True, "modifier": False}
4839
4840
4841class Max(AggFunc):
4842    arg_types = {"this": True, "expressions": False}
4843    is_var_len_args = True
4844
4845
4846class MD5(Func):
4847    _sql_names = ["MD5"]
4848
4849
4850# Represents the variant of the MD5 function that returns a binary value
4851class MD5Digest(Func):
4852    _sql_names = ["MD5_DIGEST"]
4853
4854
4855class Min(AggFunc):
4856    arg_types = {"this": True, "expressions": False}
4857    is_var_len_args = True
4858
4859
4860class Month(Func):
4861    pass
4862
4863
4864class Nvl2(Func):
4865    arg_types = {"this": True, "true": True, "false": False}
4866
4867
4868# https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-predict#mlpredict_function
4869class Predict(Func):
4870    arg_types = {"this": True, "expression": True, "params_struct": False}
4871
4872
4873class Pow(Binary, Func):
4874    _sql_names = ["POWER", "POW"]
4875
4876
4877class PercentileCont(AggFunc):
4878    arg_types = {"this": True, "expression": False}
4879
4880
4881class PercentileDisc(AggFunc):
4882    arg_types = {"this": True, "expression": False}
4883
4884
4885class Quantile(AggFunc):
4886    arg_types = {"this": True, "quantile": True}
4887
4888
4889class ApproxQuantile(Quantile):
4890    arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
4891
4892
4893class RangeN(Func):
4894    arg_types = {"this": True, "expressions": True, "each": False}
4895
4896
4897class ReadCSV(Func):
4898    _sql_names = ["READ_CSV"]
4899    is_var_len_args = True
4900    arg_types = {"this": True, "expressions": False}
4901
4902
4903class Reduce(Func):
4904    arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
4905
4906
4907class RegexpExtract(Func):
4908    arg_types = {
4909        "this": True,
4910        "expression": True,
4911        "position": False,
4912        "occurrence": False,
4913        "parameters": False,
4914        "group": False,
4915    }
4916
4917
4918class RegexpReplace(Func):
4919    arg_types = {
4920        "this": True,
4921        "expression": True,
4922        "replacement": True,
4923        "position": False,
4924        "occurrence": False,
4925        "parameters": False,
4926        "modifiers": False,
4927    }
4928
4929
4930class RegexpLike(Binary, Func):
4931    arg_types = {"this": True, "expression": True, "flag": False}
4932
4933
4934class RegexpILike(Binary, Func):
4935    arg_types = {"this": True, "expression": True, "flag": False}
4936
4937
4938# https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.functions.split.html
4939# limit is the number of times a pattern is applied
4940class RegexpSplit(Func):
4941    arg_types = {"this": True, "expression": True, "limit": False}
4942
4943
4944class Repeat(Func):
4945    arg_types = {"this": True, "times": True}
4946
4947
4948class Round(Func):
4949    arg_types = {"this": True, "decimals": False}
4950
4951
4952class RowNumber(Func):
4953    arg_types: t.Dict[str, t.Any] = {}
4954
4955
4956class SafeDivide(Func):
4957    arg_types = {"this": True, "expression": True}
4958
4959
4960class SetAgg(AggFunc):
4961    pass
4962
4963
4964class SHA(Func):
4965    _sql_names = ["SHA", "SHA1"]
4966
4967
4968class SHA2(Func):
4969    _sql_names = ["SHA2"]
4970    arg_types = {"this": True, "length": False}
4971
4972
4973class SortArray(Func):
4974    arg_types = {"this": True, "asc": False}
4975
4976
4977class Split(Func):
4978    arg_types = {"this": True, "expression": True, "limit": False}
4979
4980
4981# Start may be omitted in the case of postgres
4982# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
4983class Substring(Func):
4984    arg_types = {"this": True, "start": False, "length": False}
4985
4986
4987class StandardHash(Func):
4988    arg_types = {"this": True, "expression": False}
4989
4990
4991class StartsWith(Func):
4992    _sql_names = ["STARTS_WITH", "STARTSWITH"]
4993    arg_types = {"this": True, "expression": True}
4994
4995
4996class StrPosition(Func):
4997    arg_types = {
4998        "this": True,
4999        "substr": True,
5000        "position": False,
5001        "instance": False,
5002    }
5003
5004
5005class StrToDate(Func):
5006    arg_types = {"this": True, "format": True}
5007
5008
5009class StrToTime(Func):
5010    arg_types = {"this": True, "format": True, "zone": False}
5011
5012
5013# Spark allows unix_timestamp()
5014# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html
5015class StrToUnix(Func):
5016    arg_types = {"this": False, "format": False}
5017
5018
5019# https://prestodb.io/docs/current/functions/string.html
5020# https://spark.apache.org/docs/latest/api/sql/index.html#str_to_map
5021class StrToMap(Func):
5022    arg_types = {
5023        "this": True,
5024        "pair_delim": False,
5025        "key_value_delim": False,
5026        "duplicate_resolution_callback": False,
5027    }
5028
5029
5030class NumberToStr(Func):
5031    arg_types = {"this": True, "format": True, "culture": False}
5032
5033
5034class FromBase(Func):
5035    arg_types = {"this": True, "expression": True}
5036
5037
5038class Struct(Func):
5039    arg_types = {"expressions": True}
5040    is_var_len_args = True
5041
5042
5043class StructExtract(Func):
5044    arg_types = {"this": True, "expression": True}
5045
5046
5047# https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql?view=sql-server-ver16
5048# https://docs.snowflake.com/en/sql-reference/functions/insert
5049class Stuff(Func):
5050    _sql_names = ["STUFF", "INSERT"]
5051    arg_types = {"this": True, "start": True, "length": True, "expression": True}
5052
5053
5054class Sum(AggFunc):
5055    pass
5056
5057
5058class Sqrt(Func):
5059    pass
5060
5061
5062class Stddev(AggFunc):
5063    pass
5064
5065
5066class StddevPop(AggFunc):
5067    pass
5068
5069
5070class StddevSamp(AggFunc):
5071    pass
5072
5073
5074class TimeToStr(Func):
5075    arg_types = {"this": True, "format": True, "culture": False}
5076
5077
5078class TimeToTimeStr(Func):
5079    pass
5080
5081
5082class TimeToUnix(Func):
5083    pass
5084
5085
5086class TimeStrToDate(Func):
5087    pass
5088
5089
5090class TimeStrToTime(Func):
5091    pass
5092
5093
5094class TimeStrToUnix(Func):
5095    pass
5096
5097
5098class Trim(Func):
5099    arg_types = {
5100        "this": True,
5101        "expression": False,
5102        "position": False,
5103        "collation": False,
5104    }
5105
5106
5107class TsOrDsAdd(Func, TimeUnit):
5108    arg_types = {"this": True, "expression": True, "unit": False}
5109
5110
5111class TsOrDsToDateStr(Func):
5112    pass
5113
5114
5115class TsOrDsToDate(Func):
5116    arg_types = {"this": True, "format": False}
5117
5118
5119class TsOrDiToDi(Func):
5120    pass
5121
5122
5123class Unhex(Func):
5124    pass
5125
5126
5127class UnixToStr(Func):
5128    arg_types = {"this": True, "format": False}
5129
5130
5131# https://prestodb.io/docs/current/functions/datetime.html
5132# presto has weird zone/hours/minutes
5133class UnixToTime(Func):
5134    arg_types = {"this": True, "scale": False, "zone": False, "hours": False, "minutes": False}
5135
5136    SECONDS = Literal.string("seconds")
5137    MILLIS = Literal.string("millis")
5138    MICROS = Literal.string("micros")
5139
5140
5141class UnixToTimeStr(Func):
5142    pass
5143
5144
5145class Upper(Func):
5146    _sql_names = ["UPPER", "UCASE"]
5147
5148
5149class Variance(AggFunc):
5150    _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
5151
5152
5153class VariancePop(AggFunc):
5154    _sql_names = ["VARIANCE_POP", "VAR_POP"]
5155
5156
5157class Week(Func):
5158    arg_types = {"this": True, "mode": False}
5159
5160
5161class XMLTable(Func):
5162    arg_types = {"this": True, "passing": False, "columns": False, "by_ref": False}
5163
5164
5165class Year(Func):
5166    pass
5167
5168
5169class Use(Expression):
5170    arg_types = {"this": True, "kind": False}
5171
5172
5173class Merge(Expression):
5174    arg_types = {"this": True, "using": True, "on": True, "expressions": True}
5175
5176
5177class When(Func):
5178    arg_types = {"matched": True, "source": False, "condition": False, "then": True}
5179
5180
5181# https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnextvaluefor.html
5182# https://learn.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql?view=sql-server-ver16
5183class NextValueFor(Func):
5184    arg_types = {"this": True, "order": False}
5185
5186
5187def _norm_arg(arg):
5188    return arg.lower() if type(arg) is str else arg
5189
5190
5191ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func))
5192
5193
5194# Helpers
5195@t.overload
5196def maybe_parse(
5197    sql_or_expression: ExpOrStr,
5198    *,
5199    into: t.Type[E],
5200    dialect: DialectType = None,
5201    prefix: t.Optional[str] = None,
5202    copy: bool = False,
5203    **opts,
5204) -> E:
5205    ...
5206
5207
5208@t.overload
5209def maybe_parse(
5210    sql_or_expression: str | E,
5211    *,
5212    into: t.Optional[IntoType] = None,
5213    dialect: DialectType = None,
5214    prefix: t.Optional[str] = None,
5215    copy: bool = False,
5216    **opts,
5217) -> E:
5218    ...
5219
5220
5221def maybe_parse(
5222    sql_or_expression: ExpOrStr,
5223    *,
5224    into: t.Optional[IntoType] = None,
5225    dialect: DialectType = None,
5226    prefix: t.Optional[str] = None,
5227    copy: bool = False,
5228    **opts,
5229) -> Expression:
5230    """Gracefully handle a possible string or expression.
5231
5232    Example:
5233        >>> maybe_parse("1")
5234        (LITERAL this: 1, is_string: False)
5235        >>> maybe_parse(to_identifier("x"))
5236        (IDENTIFIER this: x, quoted: False)
5237
5238    Args:
5239        sql_or_expression: the SQL code string or an expression
5240        into: the SQLGlot Expression to parse into
5241        dialect: the dialect used to parse the input expressions (in the case that an
5242            input expression is a SQL string).
5243        prefix: a string to prefix the sql with before it gets parsed
5244            (automatically includes a space)
5245        copy: whether or not to copy the expression.
5246        **opts: other options to use to parse the input expressions (again, in the case
5247            that an input expression is a SQL string).
5248
5249    Returns:
5250        Expression: the parsed or given expression.
5251    """
5252    if isinstance(sql_or_expression, Expression):
5253        if copy:
5254            return sql_or_expression.copy()
5255        return sql_or_expression
5256
5257    if sql_or_expression is None:
5258        raise ParseError(f"SQL cannot be None")
5259
5260    import sqlglot
5261
5262    sql = str(sql_or_expression)
5263    if prefix:
5264        sql = f"{prefix} {sql}"
5265
5266    return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
5267
5268
5269@t.overload
5270def maybe_copy(instance: None, copy: bool = True) -> None:
5271    ...
5272
5273
5274@t.overload
5275def maybe_copy(instance: E, copy: bool = True) -> E:
5276    ...
5277
5278
5279def maybe_copy(instance, copy=True):
5280    return instance.copy() if copy and instance else instance
5281
5282
5283def _is_wrong_expression(expression, into):
5284    return isinstance(expression, Expression) and not isinstance(expression, into)
5285
5286
5287def _apply_builder(
5288    expression,
5289    instance,
5290    arg,
5291    copy=True,
5292    prefix=None,
5293    into=None,
5294    dialect=None,
5295    into_arg="this",
5296    **opts,
5297):
5298    if _is_wrong_expression(expression, into):
5299        expression = into(**{into_arg: expression})
5300    instance = maybe_copy(instance, copy)
5301    expression = maybe_parse(
5302        sql_or_expression=expression,
5303        prefix=prefix,
5304        into=into,
5305        dialect=dialect,
5306        **opts,
5307    )
5308    instance.set(arg, expression)
5309    return instance
5310
5311
5312def _apply_child_list_builder(
5313    *expressions,
5314    instance,
5315    arg,
5316    append=True,
5317    copy=True,
5318    prefix=None,
5319    into=None,
5320    dialect=None,
5321    properties=None,
5322    **opts,
5323):
5324    instance = maybe_copy(instance, copy)
5325    parsed = []
5326    for expression in expressions:
5327        if expression is not None:
5328            if _is_wrong_expression(expression, into):
5329                expression = into(expressions=[expression])
5330
5331            expression = maybe_parse(
5332                expression,
5333                into=into,
5334                dialect=dialect,
5335                prefix=prefix,
5336                **opts,
5337            )
5338            parsed.extend(expression.expressions)
5339
5340    existing = instance.args.get(arg)
5341    if append and existing:
5342        parsed = existing.expressions + parsed
5343
5344    child = into(expressions=parsed)
5345    for k, v in (properties or {}).items():
5346        child.set(k, v)
5347    instance.set(arg, child)
5348
5349    return instance
5350
5351
5352def _apply_list_builder(
5353    *expressions,
5354    instance,
5355    arg,
5356    append=True,
5357    copy=True,
5358    prefix=None,
5359    into=None,
5360    dialect=None,
5361    **opts,
5362):
5363    inst = maybe_copy(instance, copy)
5364
5365    expressions = [
5366        maybe_parse(
5367            sql_or_expression=expression,
5368            into=into,
5369            prefix=prefix,
5370            dialect=dialect,
5371            **opts,
5372        )
5373        for expression in expressions
5374        if expression is not None
5375    ]
5376
5377    existing_expressions = inst.args.get(arg)
5378    if append and existing_expressions:
5379        expressions = existing_expressions + expressions
5380
5381    inst.set(arg, expressions)
5382    return inst
5383
5384
5385def _apply_conjunction_builder(
5386    *expressions,
5387    instance,
5388    arg,
5389    into=None,
5390    append=True,
5391    copy=True,
5392    dialect=None,
5393    **opts,
5394):
5395    expressions = [exp for exp in expressions if exp is not None and exp != ""]
5396    if not expressions:
5397        return instance
5398
5399    inst = maybe_copy(instance, copy)
5400
5401    existing = inst.args.get(arg)
5402    if append and existing is not None:
5403        expressions = [existing.this if into else existing] + list(expressions)
5404
5405    node = and_(*expressions, dialect=dialect, copy=copy, **opts)
5406
5407    inst.set(arg, into(this=node) if into else node)
5408    return inst
5409
5410
5411def _apply_cte_builder(
5412    instance: E,
5413    alias: ExpOrStr,
5414    as_: ExpOrStr,
5415    recursive: t.Optional[bool] = None,
5416    append: bool = True,
5417    dialect: DialectType = None,
5418    copy: bool = True,
5419    **opts,
5420) -> E:
5421    alias_expression = maybe_parse(alias, dialect=dialect, into=TableAlias, **opts)
5422    as_expression = maybe_parse(as_, dialect=dialect, **opts)
5423    cte = CTE(this=as_expression, alias=alias_expression)
5424    return _apply_child_list_builder(
5425        cte,
5426        instance=instance,
5427        arg="with",
5428        append=append,
5429        copy=copy,
5430        into=With,
5431        properties={"recursive": recursive or False},
5432    )
5433
5434
5435def _combine(
5436    expressions: t.Sequence[t.Optional[ExpOrStr]],
5437    operator: t.Type[Connector],
5438    dialect: DialectType = None,
5439    copy: bool = True,
5440    **opts,
5441) -> Expression:
5442    conditions = [
5443        condition(expression, dialect=dialect, copy=copy, **opts)
5444        for expression in expressions
5445        if expression is not None
5446    ]
5447
5448    this, *rest = conditions
5449    if rest:
5450        this = _wrap(this, Connector)
5451    for expression in rest:
5452        this = operator(this=this, expression=_wrap(expression, Connector))
5453
5454    return this
5455
5456
5457def _wrap(expression: E, kind: t.Type[Expression]) -> E | Paren:
5458    return Paren(this=expression) if isinstance(expression, kind) else expression
5459
5460
5461def union(
5462    left: ExpOrStr, right: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
5463) -> Union:
5464    """
5465    Initializes a syntax tree from one UNION expression.
5466
5467    Example:
5468        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
5469        'SELECT * FROM foo UNION SELECT * FROM bla'
5470
5471    Args:
5472        left: the SQL code string corresponding to the left-hand side.
5473            If an `Expression` instance is passed, it will be used as-is.
5474        right: the SQL code string corresponding to the right-hand side.
5475            If an `Expression` instance is passed, it will be used as-is.
5476        distinct: set the DISTINCT flag if and only if this is true.
5477        dialect: the dialect used to parse the input expression.
5478        opts: other options to use to parse the input expressions.
5479
5480    Returns:
5481        The new Union instance.
5482    """
5483    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
5484    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
5485
5486    return Union(this=left, expression=right, distinct=distinct)
5487
5488
5489def intersect(
5490    left: ExpOrStr, right: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
5491) -> Intersect:
5492    """
5493    Initializes a syntax tree from one INTERSECT expression.
5494
5495    Example:
5496        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
5497        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
5498
5499    Args:
5500        left: the SQL code string corresponding to the left-hand side.
5501            If an `Expression` instance is passed, it will be used as-is.
5502        right: the SQL code string corresponding to the right-hand side.
5503            If an `Expression` instance is passed, it will be used as-is.
5504        distinct: set the DISTINCT flag if and only if this is true.
5505        dialect: the dialect used to parse the input expression.
5506        opts: other options to use to parse the input expressions.
5507
5508    Returns:
5509        The new Intersect instance.
5510    """
5511    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
5512    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
5513
5514    return Intersect(this=left, expression=right, distinct=distinct)
5515
5516
5517def except_(
5518    left: ExpOrStr, right: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
5519) -> Except:
5520    """
5521    Initializes a syntax tree from one EXCEPT expression.
5522
5523    Example:
5524        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
5525        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
5526
5527    Args:
5528        left: the SQL code string corresponding to the left-hand side.
5529            If an `Expression` instance is passed, it will be used as-is.
5530        right: the SQL code string corresponding to the right-hand side.
5531            If an `Expression` instance is passed, it will be used as-is.
5532        distinct: set the DISTINCT flag if and only if this is true.
5533        dialect: the dialect used to parse the input expression.
5534        opts: other options to use to parse the input expressions.
5535
5536    Returns:
5537        The new Except instance.
5538    """
5539    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
5540    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
5541
5542    return Except(this=left, expression=right, distinct=distinct)
5543
5544
5545def select(*expressions: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
5546    """
5547    Initializes a syntax tree from one or multiple SELECT expressions.
5548
5549    Example:
5550        >>> select("col1", "col2").from_("tbl").sql()
5551        'SELECT col1, col2 FROM tbl'
5552
5553    Args:
5554        *expressions: the SQL code string to parse as the expressions of a
5555            SELECT statement. If an Expression instance is passed, this is used as-is.
5556        dialect: the dialect used to parse the input expressions (in the case that an
5557            input expression is a SQL string).
5558        **opts: other options to use to parse the input expressions (again, in the case
5559            that an input expression is a SQL string).
5560
5561    Returns:
5562        Select: the syntax tree for the SELECT statement.
5563    """
5564    return Select().select(*expressions, dialect=dialect, **opts)
5565
5566
5567def from_(expression: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
5568    """
5569    Initializes a syntax tree from a FROM expression.
5570
5571    Example:
5572        >>> from_("tbl").select("col1", "col2").sql()
5573        'SELECT col1, col2 FROM tbl'
5574
5575    Args:
5576        *expression: the SQL code string to parse as the FROM expressions of a
5577            SELECT statement. If an Expression instance is passed, this is used as-is.
5578        dialect: the dialect used to parse the input expression (in the case that the
5579            input expression is a SQL string).
5580        **opts: other options to use to parse the input expressions (again, in the case
5581            that the input expression is a SQL string).
5582
5583    Returns:
5584        Select: the syntax tree for the SELECT statement.
5585    """
5586    return Select().from_(expression, dialect=dialect, **opts)
5587
5588
5589def update(
5590    table: str | Table,
5591    properties: dict,
5592    where: t.Optional[ExpOrStr] = None,
5593    from_: t.Optional[ExpOrStr] = None,
5594    dialect: DialectType = None,
5595    **opts,
5596) -> Update:
5597    """
5598    Creates an update statement.
5599
5600    Example:
5601        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql()
5602        "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1"
5603
5604    Args:
5605        *properties: dictionary of properties to set which are
5606            auto converted to sql objects eg None -> NULL
5607        where: sql conditional parsed into a WHERE statement
5608        from_: sql statement parsed into a FROM statement
5609        dialect: the dialect used to parse the input expressions.
5610        **opts: other options to use to parse the input expressions.
5611
5612    Returns:
5613        Update: the syntax tree for the UPDATE statement.
5614    """
5615    update_expr = Update(this=maybe_parse(table, into=Table, dialect=dialect))
5616    update_expr.set(
5617        "expressions",
5618        [
5619            EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v))
5620            for k, v in properties.items()
5621        ],
5622    )
5623    if from_:
5624        update_expr.set(
5625            "from",
5626            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts),
5627        )
5628    if isinstance(where, Condition):
5629        where = Where(this=where)
5630    if where:
5631        update_expr.set(
5632            "where",
5633            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
5634        )
5635    return update_expr
5636
5637
5638def delete(
5639    table: ExpOrStr,
5640    where: t.Optional[ExpOrStr] = None,
5641    returning: t.Optional[ExpOrStr] = None,
5642    dialect: DialectType = None,
5643    **opts,
5644) -> Delete:
5645    """
5646    Builds a delete statement.
5647
5648    Example:
5649        >>> delete("my_table", where="id > 1").sql()
5650        'DELETE FROM my_table WHERE id > 1'
5651
5652    Args:
5653        where: sql conditional parsed into a WHERE statement
5654        returning: sql conditional parsed into a RETURNING statement
5655        dialect: the dialect used to parse the input expressions.
5656        **opts: other options to use to parse the input expressions.
5657
5658    Returns:
5659        Delete: the syntax tree for the DELETE statement.
5660    """
5661    delete_expr = Delete().delete(table, dialect=dialect, copy=False, **opts)
5662    if where:
5663        delete_expr = delete_expr.where(where, dialect=dialect, copy=False, **opts)
5664    if returning:
5665        delete_expr = delete_expr.returning(returning, dialect=dialect, copy=False, **opts)
5666    return delete_expr
5667
5668
5669def insert(
5670    expression: ExpOrStr,
5671    into: ExpOrStr,
5672    columns: t.Optional[t.Sequence[ExpOrStr]] = None,
5673    overwrite: t.Optional[bool] = None,
5674    dialect: DialectType = None,
5675    copy: bool = True,
5676    **opts,
5677) -> Insert:
5678    """
5679    Builds an INSERT statement.
5680
5681    Example:
5682        >>> insert("VALUES (1, 2, 3)", "tbl").sql()
5683        'INSERT INTO tbl VALUES (1, 2, 3)'
5684
5685    Args:
5686        expression: the sql string or expression of the INSERT statement
5687        into: the tbl to insert data to.
5688        columns: optionally the table's column names.
5689        overwrite: whether to INSERT OVERWRITE or not.
5690        dialect: the dialect used to parse the input expressions.
5691        copy: whether or not to copy the expression.
5692        **opts: other options to use to parse the input expressions.
5693
5694    Returns:
5695        Insert: the syntax tree for the INSERT statement.
5696    """
5697    expr = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
5698    this: Table | Schema = maybe_parse(into, into=Table, dialect=dialect, copy=copy, **opts)
5699
5700    if columns:
5701        this = _apply_list_builder(
5702            *columns,
5703            instance=Schema(this=this),
5704            arg="expressions",
5705            into=Identifier,
5706            copy=False,
5707            dialect=dialect,
5708            **opts,
5709        )
5710
5711    return Insert(this=this, expression=expr, overwrite=overwrite)
5712
5713
5714def condition(
5715    expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
5716) -> Condition:
5717    """
5718    Initialize a logical condition expression.
5719
5720    Example:
5721        >>> condition("x=1").sql()
5722        'x = 1'
5723
5724        This is helpful for composing larger logical syntax trees:
5725        >>> where = condition("x=1")
5726        >>> where = where.and_("y=1")
5727        >>> Select().from_("tbl").select("*").where(where).sql()
5728        'SELECT * FROM tbl WHERE x = 1 AND y = 1'
5729
5730    Args:
5731        *expression: the SQL code string to parse.
5732            If an Expression instance is passed, this is used as-is.
5733        dialect: the dialect used to parse the input expression (in the case that the
5734            input expression is a SQL string).
5735        copy: Whether or not to copy `expression` (only applies to expressions).
5736        **opts: other options to use to parse the input expressions (again, in the case
5737            that the input expression is a SQL string).
5738
5739    Returns:
5740        The new Condition instance
5741    """
5742    return maybe_parse(
5743        expression,
5744        into=Condition,
5745        dialect=dialect,
5746        copy=copy,
5747        **opts,
5748    )
5749
5750
5751def and_(
5752    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
5753) -> Condition:
5754    """
5755    Combine multiple conditions with an AND logical operator.
5756
5757    Example:
5758        >>> and_("x=1", and_("y=1", "z=1")).sql()
5759        'x = 1 AND (y = 1 AND z = 1)'
5760
5761    Args:
5762        *expressions: the SQL code strings to parse.
5763            If an Expression instance is passed, this is used as-is.
5764        dialect: the dialect used to parse the input expression.
5765        copy: whether or not to copy `expressions` (only applies to Expressions).
5766        **opts: other options to use to parse the input expressions.
5767
5768    Returns:
5769        And: the new condition
5770    """
5771    return t.cast(Condition, _combine(expressions, And, dialect, copy=copy, **opts))
5772
5773
5774def or_(
5775    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
5776) -> Condition:
5777    """
5778    Combine multiple conditions with an OR logical operator.
5779
5780    Example:
5781        >>> or_("x=1", or_("y=1", "z=1")).sql()
5782        'x = 1 OR (y = 1 OR z = 1)'
5783
5784    Args:
5785        *expressions: the SQL code strings to parse.
5786            If an Expression instance is passed, this is used as-is.
5787        dialect: the dialect used to parse the input expression.
5788        copy: whether or not to copy `expressions` (only applies to Expressions).
5789        **opts: other options to use to parse the input expressions.
5790
5791    Returns:
5792        Or: the new condition
5793    """
5794    return t.cast(Condition, _combine(expressions, Or, dialect, copy=copy, **opts))
5795
5796
5797def not_(expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts) -> Not:
5798    """
5799    Wrap a condition with a NOT operator.
5800
5801    Example:
5802        >>> not_("this_suit='black'").sql()
5803        "NOT this_suit = 'black'"
5804
5805    Args:
5806        expression: the SQL code string to parse.
5807            If an Expression instance is passed, this is used as-is.
5808        dialect: the dialect used to parse the input expression.
5809        copy: whether to copy the expression or not.
5810        **opts: other options to use to parse the input expressions.
5811
5812    Returns:
5813        The new condition.
5814    """
5815    this = condition(
5816        expression,
5817        dialect=dialect,
5818        copy=copy,
5819        **opts,
5820    )
5821    return Not(this=_wrap(this, Connector))
5822
5823
5824def paren(expression: ExpOrStr, copy: bool = True) -> Paren:
5825    """
5826    Wrap an expression in parentheses.
5827
5828    Example:
5829        >>> paren("5 + 3").sql()
5830        '(5 + 3)'
5831
5832    Args:
5833        expression: the SQL code string to parse.
5834            If an Expression instance is passed, this is used as-is.
5835        copy: whether to copy the expression or not.
5836
5837    Returns:
5838        The wrapped expression.
5839    """
5840    return Paren(this=maybe_parse(expression, copy=copy))
5841
5842
5843SAFE_IDENTIFIER_RE = re.compile(r"^[_a-zA-Z][\w]*$")
5844
5845
5846@t.overload
5847def to_identifier(name: None, quoted: t.Optional[bool] = None, copy: bool = True) -> None:
5848    ...
5849
5850
5851@t.overload
5852def to_identifier(
5853    name: str | Identifier, quoted: t.Optional[bool] = None, copy: bool = True
5854) -> Identifier:
5855    ...
5856
5857
5858def to_identifier(name, quoted=None, copy=True):
5859    """Builds an identifier.
5860
5861    Args:
5862        name: The name to turn into an identifier.
5863        quoted: Whether or not force quote the identifier.
5864        copy: Whether or not to copy a passed in Identefier node.
5865
5866    Returns:
5867        The identifier ast node.
5868    """
5869
5870    if name is None:
5871        return None
5872
5873    if isinstance(name, Identifier):
5874        identifier = maybe_copy(name, copy)
5875    elif isinstance(name, str):
5876        identifier = Identifier(
5877            this=name,
5878            quoted=not SAFE_IDENTIFIER_RE.match(name) if quoted is None else quoted,
5879        )
5880    else:
5881        raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
5882    return identifier
5883
5884
5885INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*")
5886
5887
5888def to_interval(interval: str | Literal) -> Interval:
5889    """Builds an interval expression from a string like '1 day' or '5 months'."""
5890    if isinstance(interval, Literal):
5891        if not interval.is_string:
5892            raise ValueError("Invalid interval string.")
5893
5894        interval = interval.this
5895
5896    interval_parts = INTERVAL_STRING_RE.match(interval)  # type: ignore
5897
5898    if not interval_parts:
5899        raise ValueError("Invalid interval string.")
5900
5901    return Interval(
5902        this=Literal.string(interval_parts.group(1)),
5903        unit=Var(this=interval_parts.group(2)),
5904    )
5905
5906
5907@t.overload
5908def to_table(sql_path: str | Table, **kwargs) -> Table:
5909    ...
5910
5911
5912@t.overload
5913def to_table(sql_path: None, **kwargs) -> None:
5914    ...
5915
5916
5917def to_table(
5918    sql_path: t.Optional[str | Table], dialect: DialectType = None, **kwargs
5919) -> t.Optional[Table]:
5920    """
5921    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
5922    If a table is passed in then that table is returned.
5923
5924    Args:
5925        sql_path: a `[catalog].[schema].[table]` string.
5926        dialect: the source dialect according to which the table name will be parsed.
5927        kwargs: the kwargs to instantiate the resulting `Table` expression with.
5928
5929    Returns:
5930        A table expression.
5931    """
5932    if sql_path is None or isinstance(sql_path, Table):
5933        return sql_path
5934    if not isinstance(sql_path, str):
5935        raise ValueError(f"Invalid type provided for a table: {type(sql_path)}")
5936
5937    table = maybe_parse(sql_path, into=Table, dialect=dialect)
5938    if table:
5939        for k, v in kwargs.items():
5940            table.set(k, v)
5941
5942    return table
5943
5944
5945def to_column(sql_path: str | Column, **kwargs) -> Column:
5946    """
5947    Create a column from a `[table].[column]` sql path. Schema is optional.
5948
5949    If a column is passed in then that column is returned.
5950
5951    Args:
5952        sql_path: `[table].[column]` string
5953    Returns:
5954        Table: A column expression
5955    """
5956    if sql_path is None or isinstance(sql_path, Column):
5957        return sql_path
5958    if not isinstance(sql_path, str):
5959        raise ValueError(f"Invalid type provided for column: {type(sql_path)}")
5960    return column(*reversed(sql_path.split(".")), **kwargs)  # type: ignore
5961
5962
5963def alias_(
5964    expression: ExpOrStr,
5965    alias: str | Identifier,
5966    table: bool | t.Sequence[str | Identifier] = False,
5967    quoted: t.Optional[bool] = None,
5968    dialect: DialectType = None,
5969    copy: bool = True,
5970    **opts,
5971):
5972    """Create an Alias expression.
5973
5974    Example:
5975        >>> alias_('foo', 'bar').sql()
5976        'foo AS bar'
5977
5978        >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql()
5979        '(SELECT 1, 2) AS bar(a, b)'
5980
5981    Args:
5982        expression: the SQL code strings to parse.
5983            If an Expression instance is passed, this is used as-is.
5984        alias: the alias name to use. If the name has
5985            special characters it is quoted.
5986        table: Whether or not to create a table alias, can also be a list of columns.
5987        quoted: whether or not to quote the alias
5988        dialect: the dialect used to parse the input expression.
5989        copy: Whether or not to copy the expression.
5990        **opts: other options to use to parse the input expressions.
5991
5992    Returns:
5993        Alias: the aliased expression
5994    """
5995    exp = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
5996    alias = to_identifier(alias, quoted=quoted)
5997
5998    if table:
5999        table_alias = TableAlias(this=alias)
6000        exp.set("alias", table_alias)
6001
6002        if not isinstance(table, bool):
6003            for column in table:
6004                table_alias.append("columns", to_identifier(column, quoted=quoted))
6005
6006        return exp
6007
6008    # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in
6009    # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node
6010    # for the complete Window expression.
6011    #
6012    # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
6013
6014    if "alias" in exp.arg_types and not isinstance(exp, Window):
6015        exp.set("alias", alias)
6016        return exp
6017    return Alias(this=exp, alias=alias)
6018
6019
6020def subquery(
6021    expression: ExpOrStr,
6022    alias: t.Optional[Identifier | str] = None,
6023    dialect: DialectType = None,
6024    **opts,
6025) -> Select:
6026    """
6027    Build a subquery expression.
6028
6029    Example:
6030        >>> subquery('select x from tbl', 'bar').select('x').sql()
6031        'SELECT x FROM (SELECT x FROM tbl) AS bar'
6032
6033    Args:
6034        expression: the SQL code strings to parse.
6035            If an Expression instance is passed, this is used as-is.
6036        alias: the alias name to use.
6037        dialect: the dialect used to parse the input expression.
6038        **opts: other options to use to parse the input expressions.
6039
6040    Returns:
6041        A new Select instance with the subquery expression included.
6042    """
6043
6044    expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias)
6045    return Select().from_(expression, dialect=dialect, **opts)
6046
6047
6048def column(
6049    col: str | Identifier,
6050    table: t.Optional[str | Identifier] = None,
6051    db: t.Optional[str | Identifier] = None,
6052    catalog: t.Optional[str | Identifier] = None,
6053    quoted: t.Optional[bool] = None,
6054) -> Column:
6055    """
6056    Build a Column.
6057
6058    Args:
6059        col: Column name.
6060        table: Table name.
6061        db: Database name.
6062        catalog: Catalog name.
6063        quoted: Whether to force quotes on the column's identifiers.
6064
6065    Returns:
6066        The new Column instance.
6067    """
6068    return Column(
6069        this=to_identifier(col, quoted=quoted),
6070        table=to_identifier(table, quoted=quoted),
6071        db=to_identifier(db, quoted=quoted),
6072        catalog=to_identifier(catalog, quoted=quoted),
6073    )
6074
6075
6076def cast(expression: ExpOrStr, to: str | DataType | DataType.Type, **opts) -> Cast:
6077    """Cast an expression to a data type.
6078
6079    Example:
6080        >>> cast('x + 1', 'int').sql()
6081        'CAST(x + 1 AS INT)'
6082
6083    Args:
6084        expression: The expression to cast.
6085        to: The datatype to cast to.
6086
6087    Returns:
6088        The new Cast instance.
6089    """
6090    expression = maybe_parse(expression, **opts)
6091    data_type = DataType.build(to, **opts)
6092    expression = Cast(this=expression, to=data_type)
6093    expression.type = data_type
6094    return expression
6095
6096
6097def table_(
6098    table: Identifier | str,
6099    db: t.Optional[Identifier | str] = None,
6100    catalog: t.Optional[Identifier | str] = None,
6101    quoted: t.Optional[bool] = None,
6102    alias: t.Optional[Identifier | str] = None,
6103) -> Table:
6104    """Build a Table.
6105
6106    Args:
6107        table: Table name.
6108        db: Database name.
6109        catalog: Catalog name.
6110        quote: Whether to force quotes on the table's identifiers.
6111        alias: Table's alias.
6112
6113    Returns:
6114        The new Table instance.
6115    """
6116    return Table(
6117        this=to_identifier(table, quoted=quoted) if table else None,
6118        db=to_identifier(db, quoted=quoted) if db else None,
6119        catalog=to_identifier(catalog, quoted=quoted) if catalog else None,
6120        alias=TableAlias(this=to_identifier(alias)) if alias else None,
6121    )
6122
6123
6124def values(
6125    values: t.Iterable[t.Tuple[t.Any, ...]],
6126    alias: t.Optional[str] = None,
6127    columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None,
6128) -> Values:
6129    """Build VALUES statement.
6130
6131    Example:
6132        >>> values([(1, '2')]).sql()
6133        "VALUES (1, '2')"
6134
6135    Args:
6136        values: values statements that will be converted to SQL
6137        alias: optional alias
6138        columns: Optional list of ordered column names or ordered dictionary of column names to types.
6139         If either are provided then an alias is also required.
6140
6141    Returns:
6142        Values: the Values expression object
6143    """
6144    if columns and not alias:
6145        raise ValueError("Alias is required when providing columns")
6146
6147    return Values(
6148        expressions=[convert(tup) for tup in values],
6149        alias=(
6150            TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
6151            if columns
6152            else (TableAlias(this=to_identifier(alias)) if alias else None)
6153        ),
6154    )
6155
6156
6157def var(name: t.Optional[ExpOrStr]) -> Var:
6158    """Build a SQL variable.
6159
6160    Example:
6161        >>> repr(var('x'))
6162        '(VAR this: x)'
6163
6164        >>> repr(var(column('x', table='y')))
6165        '(VAR this: x)'
6166
6167    Args:
6168        name: The name of the var or an expression who's name will become the var.
6169
6170    Returns:
6171        The new variable node.
6172    """
6173    if not name:
6174        raise ValueError("Cannot convert empty name into var.")
6175
6176    if isinstance(name, Expression):
6177        name = name.name
6178    return Var(this=name)
6179
6180
6181def rename_table(old_name: str | Table, new_name: str | Table) -> AlterTable:
6182    """Build ALTER TABLE... RENAME... expression
6183
6184    Args:
6185        old_name: The old name of the table
6186        new_name: The new name of the table
6187
6188    Returns:
6189        Alter table expression
6190    """
6191    old_table = to_table(old_name)
6192    new_table = to_table(new_name)
6193    return AlterTable(
6194        this=old_table,
6195        actions=[
6196            RenameTable(this=new_table),
6197        ],
6198    )
6199
6200
6201def convert(value: t.Any, copy: bool = False) -> Expression:
6202    """Convert a python value into an expression object.
6203
6204    Raises an error if a conversion is not possible.
6205
6206    Args:
6207        value: A python object.
6208        copy: Whether or not to copy `value` (only applies to Expressions and collections).
6209
6210    Returns:
6211        Expression: the equivalent expression object.
6212    """
6213    if isinstance(value, Expression):
6214        return maybe_copy(value, copy)
6215    if isinstance(value, str):
6216        return Literal.string(value)
6217    if isinstance(value, bool):
6218        return Boolean(this=value)
6219    if value is None or (isinstance(value, float) and math.isnan(value)):
6220        return NULL
6221    if isinstance(value, numbers.Number):
6222        return Literal.number(value)
6223    if isinstance(value, datetime.datetime):
6224        datetime_literal = Literal.string(
6225            (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat()
6226        )
6227        return TimeStrToTime(this=datetime_literal)
6228    if isinstance(value, datetime.date):
6229        date_literal = Literal.string(value.strftime("%Y-%m-%d"))
6230        return DateStrToDate(this=date_literal)
6231    if isinstance(value, tuple):
6232        return Tuple(expressions=[convert(v, copy=copy) for v in value])
6233    if isinstance(value, list):
6234        return Array(expressions=[convert(v, copy=copy) for v in value])
6235    if isinstance(value, dict):
6236        return Map(
6237            keys=Array(expressions=[convert(k, copy=copy) for k in value]),
6238            values=Array(expressions=[convert(v, copy=copy) for v in value.values()]),
6239        )
6240    raise ValueError(f"Cannot convert {value}")
6241
6242
6243def replace_children(expression: Expression, fun: t.Callable, *args, **kwargs) -> None:
6244    """
6245    Replace children of an expression with the result of a lambda fun(child) -> exp.
6246    """
6247    for k, v in expression.args.items():
6248        is_list_arg = type(v) is list
6249
6250        child_nodes = v if is_list_arg else [v]
6251        new_child_nodes = []
6252
6253        for cn in child_nodes:
6254            if isinstance(cn, Expression):
6255                for child_node in ensure_collection(fun(cn, *args, **kwargs)):
6256                    new_child_nodes.append(child_node)
6257                    child_node.parent = expression
6258                    child_node.arg_key = k
6259            else:
6260                new_child_nodes.append(cn)
6261
6262        expression.args[k] = new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0)
6263
6264
6265def column_table_names(expression: Expression, exclude: str = "") -> t.Set[str]:
6266    """
6267    Return all table names referenced through columns in an expression.
6268
6269    Example:
6270        >>> import sqlglot
6271        >>> sorted(column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e")))
6272        ['a', 'c']
6273
6274    Args:
6275        expression: expression to find table names.
6276        exclude: a table name to exclude
6277
6278    Returns:
6279        A list of unique names.
6280    """
6281    return {
6282        table
6283        for table in (column.table for column in expression.find_all(Column))
6284        if table and table != exclude
6285    }
6286
6287
6288def table_name(table: Table | str, dialect: DialectType = None) -> str:
6289    """Get the full name of a table as a string.
6290
6291    Args:
6292        table: Table expression node or string.
6293        dialect: The dialect to generate the table name for.
6294
6295    Examples:
6296        >>> from sqlglot import exp, parse_one
6297        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
6298        'a.b.c'
6299
6300    Returns:
6301        The table name.
6302    """
6303
6304    table = maybe_parse(table, into=Table, dialect=dialect)
6305
6306    if not table:
6307        raise ValueError(f"Cannot parse {table}")
6308
6309    return ".".join(
6310        part.sql(dialect=dialect, identify=True)
6311        if not SAFE_IDENTIFIER_RE.match(part.name)
6312        else part.name
6313        for part in table.parts
6314    )
6315
6316
6317def replace_tables(expression: E, mapping: t.Dict[str, str], copy: bool = True) -> E:
6318    """Replace all tables in expression according to the mapping.
6319
6320    Args:
6321        expression: expression node to be transformed and replaced.
6322        mapping: mapping of table names.
6323        copy: whether or not to copy the expression.
6324
6325    Examples:
6326        >>> from sqlglot import exp, parse_one
6327        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
6328        'SELECT * FROM c'
6329
6330    Returns:
6331        The mapped expression.
6332    """
6333
6334    def _replace_tables(node: Expression) -> Expression:
6335        if isinstance(node, Table):
6336            new_name = mapping.get(table_name(node))
6337            if new_name:
6338                return to_table(
6339                    new_name,
6340                    **{k: v for k, v in node.args.items() if k not in ("this", "db", "catalog")},
6341                )
6342        return node
6343
6344    return expression.transform(_replace_tables, copy=copy)
6345
6346
6347def replace_placeholders(expression: Expression, *args, **kwargs) -> Expression:
6348    """Replace placeholders in an expression.
6349
6350    Args:
6351        expression: expression node to be transformed and replaced.
6352        args: positional names that will substitute unnamed placeholders in the given order.
6353        kwargs: keyword arguments that will substitute named placeholders.
6354
6355    Examples:
6356        >>> from sqlglot import exp, parse_one
6357        >>> replace_placeholders(
6358        ...     parse_one("select * from :tbl where ? = ?"),
6359        ...     exp.to_identifier("str_col"), "b", tbl=exp.to_identifier("foo")
6360        ... ).sql()
6361        "SELECT * FROM foo WHERE str_col = 'b'"
6362
6363    Returns:
6364        The mapped expression.
6365    """
6366
6367    def _replace_placeholders(node: Expression, args, **kwargs) -> Expression:
6368        if isinstance(node, Placeholder):
6369            if node.name:
6370                new_name = kwargs.get(node.name)
6371                if new_name:
6372                    return convert(new_name)
6373            else:
6374                try:
6375                    return convert(next(args))
6376                except StopIteration:
6377                    pass
6378        return node
6379
6380    return expression.transform(_replace_placeholders, iter(args), **kwargs)
6381
6382
6383def expand(
6384    expression: Expression, sources: t.Dict[str, Subqueryable], copy: bool = True
6385) -> Expression:
6386    """Transforms an expression by expanding all referenced sources into subqueries.
6387
6388    Examples:
6389        >>> from sqlglot import parse_one
6390        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
6391        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
6392
6393        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y"), "y": parse_one("select * from z")}).sql()
6394        'SELECT * FROM (SELECT * FROM (SELECT * FROM z) AS y /* source: y */) AS z /* source: x */'
6395
6396    Args:
6397        expression: The expression to expand.
6398        sources: A dictionary of name to Subqueryables.
6399        copy: Whether or not to copy the expression during transformation. Defaults to True.
6400
6401    Returns:
6402        The transformed expression.
6403    """
6404
6405    def _expand(node: Expression):
6406        if isinstance(node, Table):
6407            name = table_name(node)
6408            source = sources.get(name)
6409            if source:
6410                subquery = source.subquery(node.alias or name)
6411                subquery.comments = [f"source: {name}"]
6412                return subquery.transform(_expand, copy=False)
6413        return node
6414
6415    return expression.transform(_expand, copy=copy)
6416
6417
6418def func(name: str, *args, dialect: DialectType = None, **kwargs) -> Func:
6419    """
6420    Returns a Func expression.
6421
6422    Examples:
6423        >>> func("abs", 5).sql()
6424        'ABS(5)'
6425
6426        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
6427        'CAST(5 AS DOUBLE)'
6428
6429    Args:
6430        name: the name of the function to build.
6431        args: the args used to instantiate the function of interest.
6432        dialect: the source dialect.
6433        kwargs: the kwargs used to instantiate the function of interest.
6434
6435    Note:
6436        The arguments `args` and `kwargs` are mutually exclusive.
6437
6438    Returns:
6439        An instance of the function of interest, or an anonymous function, if `name` doesn't
6440        correspond to an existing `sqlglot.expressions.Func` class.
6441    """
6442    if args and kwargs:
6443        raise ValueError("Can't use both args and kwargs to instantiate a function.")
6444
6445    from sqlglot.dialects.dialect import Dialect
6446
6447    converted: t.List[Expression] = [maybe_parse(arg, dialect=dialect) for arg in args]
6448    kwargs = {key: maybe_parse(value, dialect=dialect) for key, value in kwargs.items()}
6449
6450    parser = Dialect.get_or_raise(dialect)().parser()
6451    from_args_list = parser.FUNCTIONS.get(name.upper())
6452
6453    if from_args_list:
6454        function = from_args_list(converted) if converted else from_args_list.__self__(**kwargs)  # type: ignore
6455    else:
6456        kwargs = kwargs or {"expressions": converted}
6457        function = Anonymous(this=name, **kwargs)
6458
6459    for error_message in function.error_messages(converted):
6460        raise ValueError(error_message)
6461
6462    return function
6463
6464
6465def true() -> Boolean:
6466    """
6467    Returns a true Boolean expression.
6468    """
6469    return Boolean(this=True)
6470
6471
6472def false() -> Boolean:
6473    """
6474    Returns a false Boolean expression.
6475    """
6476    return Boolean(this=False)
6477
6478
6479def null() -> Null:
6480    """
6481    Returns a Null expression.
6482    """
6483    return Null()
6484
6485
6486# TODO: deprecate this
6487TRUE = Boolean(this=True)
6488FALSE = Boolean(this=False)
6489NULL = Null()